Regular scared, like for a thunderstorm, can be fixed by finding a combination of happiness moodlets on the lot adding up to least +1, preferably more, and a shower that imparts a positive emotion.
Utterly terrified, like if they've run into something spooky, that's when panicked poops and hiding under the covers are the best medicine. Or you could spend the 8,000 reward points for "Brave" (or the far less expensive "Storm Chaser" if your sims' fear source is weather-related.)