Yellow Toad is part of the Toad Brigade and depicted as rather lazy. He spents most of his time sleeping or spinning around.
Yellow Toad is often seen sleeping, and when he is awake, he often behaves careless and rather does something else than do what he's supposed to do. In Super Mario Galaxy, during the Purple Coin challenge in Deep Dark Galaxy, he can be seen dancing in a sort of spotlight while the other members of the Toad Brigade are searching for the purple coins.
Something peculiar about him is that often while sleeping he may give the player hints and tell the player things that have yet to come. It seems as if he can see the future in his dreams.
Yellow Toad can be seen sleeping near the garage on the Comet Observatory. Once the final boss galaxy is unlocked, he mumbles "Bowser's weakness is..ZZZZ..." while sleeping, which is yet another example of his ability to see the future.
On Starship Mario, he stands near the right ear of the starship. In this game, he isn't as careless sometimes as he was in the original Super Mario Galaxy, being rather serious at times.
He can sometimes be seen sleeping, in certain levels. Other than that, he does not really have another role.