SAS modules, by themselves, will do nothing to keep your ship pointing up.
You need an ASAS to command the SAS modules in order to have some form of auto-pilot. The only thing that SAS modules do is increase the amount of torque available for rotation. This is beneficial for reducing roll during launch (if you don't use aerodynamic control surfaces), and for pitching and yawing a large spacecraft once in space without using RCS or thrust vectoring engines.
Having said that, I don't use SAS modules except in extreme cases. You'll find that many KSP YouTubers don't either, since there really isn't that much of a benefit to them. During launch, you're better served by thrust vectoring and aerodynamic surfaces than SAS, and in orbit, you should have more than enough time to orient even large vessels with just the torque provided by the command pod, with maybe a little help from RCS. Finally, SAS does nothing for you in terms of docking craft together, especially since you're already going to have RCS. In short, they're practically useless.
ASAS, on the other hand, is incredibly useful. ASAS will send commands to aerodynamic control surfaces, thrust vectoring engines, RCS, and yes, even SAS modules in order to keep the ship pointed in a given direction. It allows you to do launches without having to constantly correct your ship's trajectory. You really want one of these on most ships that you launch (exceptions to every rule, and so on).
Version 0.21 has drastically changed the stabilization and autopilot mechanics previously associated with SAS and ASAS. What used to be SAS are now just reaction wheels, increasing the available torque to your vessel. These are now even more useless on their own, since they no longer have the PD controller that would dampen (although not stop) rotation.
All types of reaction wheels, both discrete modules and those built into command pods and similar, now consume your ships available electric charge when operating and running out of charge will disable them.
The replacements for the ASAS are even better. Applying a control input will "disable" the PID controller for that axis,* allowing you to rotate your vessel without disabling the autopilot. The PID controller also seems to be better tuned (perhaps even auto-tuning itself) to various vessels, resulting in less oscillation and better control in general.
The information above still applies. Reaction wheels, by themselves, are useless. Get a guidance computer (or whatever they're called) instead.
*I'm not totally convinced that this is the case, since the PID controllers are apparently based on angular velocity and not heading. (How they managed to hold a heading on an angular velocity setpoint is beyond me, but I also wasn't the greatest at control systems either.) I think it's more likely that the setpoint angular velocity is being changed in response to control input.
