These are the stores I know about in the Bay Area.
Artfibers - I absolutely love this store! The yarn they carry is manufactured for their store alone. They allow you to sample the yarns before you buy them; you can park yourself in their sitting area and knit up a swatch from the yarn samples they have. You can learn more about Artfibers through this review.
I have to admit, with the exception of Artfibers, I have not shopped for yarn in San Francisco. I have heard good things about Noe Knit. The review from Knitter's Review. If you find yourself in the Castro District, you may want to drop in on ImagiKnit. I only know about this store by word of mouth and this review.
Article Pract is easily my hands-down favorite yarn store in the East Bay, if not the entire Bay Area! I know I am not alone in this thought, as the store is frequently mentioned in glowing terms in the Bay Area blogs that I read. Article Pract has a great selection of yarn, no matter what your price point is, and I've never gotten that "yarn store attitude" thrown at me. They are located in the Temescal section of Oakland, on the Oakland/Berkeley border. Another great feature of this neighborhood is Bakesale Betty, which is just a few doors down!
On the rare occasions that Article Pract doesn't have what I am seeking (and I need it right away), I have also dropped in on the following stores:
Stash - located on Berkeley's Solano Avenue, which is a great street to explore. You can spend the day poking around the boutiques, drinking coffee at Peet's, and eating pizza at Zachary's. Oh, but this was supposed to be about the yarn...Here's another blogger's review of Stash.Knit This, Purl That! - located just off Main Street in Pleasanton, this store has a little bit of everything. If you like Noni patterns, you can find most of them here.
Most of my yarn buying is restricted to the East Bay, but I have also heard a lot about the following stores:
North Bay - Marin Fiber Arts located in San Rafael. I haven't had a chance to go to this store, but you can see what's in stock on the store's blog.
That's the end of my list, can you think of any I need to add?