Most of the US has terrible public transit. The reason is simple: America is not densely populated. NYC is one of the few cities where public transit works, because it is so dense. Manhattan has ~70k people / sq mile. Compare this to SF: a measly ~17k people / sq mile [1] [2]. That's more than 4x as dense! The Valley is even worse, with sprawling suburbia.

Honestly, Caltrain is not that terrible. It compares fairly well to Metro-North in NYC when you consider the population density. There's a train every hour to take you up to the city (or back) in an hour. Most cities don't have a direct train you can feasibly take in from the suburbs to the city. Yeah, it sucks that it stops at midnight, but public transit generally doesn't run late anywhere. Even in NYC, transit out to the suburbs stops at ~2:30a.

It's a little annoying to see people commenting here that this is entirely due to politicians and bureaucracy. It isn't. It's just not economically feasible to offer public transit comparable to NYC to the bay area.

[1] http://en.wikipedia.org/wiki/Manhattan [2] http://en.wikipedia.org/wiki/San_Francisco