I gave Silent Hill 2 a try on GameTap awhile back (probably the cheapest way to give it a spin, BTW). Indeed, the atmosphere was thick, and the monsters were pretty creepy -- and, as somebody who desensitized himself to System Shock 2 cyborg midwives, that's saying something.
However, I couldn't finish the game. It wasn't so much the fear factor (which prevented me from finishing the aforementioned SS 2 for years) so much as the strange Resident Evil-esque logic to the puzzles. It was good enough that I didn't want to ruin it with a walkthrough, but getting stuck was extremely easy to do. The solution may well involved pixel hunting or finding some barely visible corridor somewhere. Maybe this could be attributed to being part of the nightmarish quality of the game, but eventually my level of frustration exceeded my level of fear of the game, and now the game sits rather low on my priority list of things to savor.
So I'm thinking that a great deal of Yahtzee's love from the game comes from the fundamental fact that he's probably learned all the puzzles and is rarely ever stuck. The main glaring problem with the game thus slides neatly under his radar.
That said, any game that has (massive spoiler) a secret ending like this [http://www.youtube.com/watch?v=sfUhWPKMtEc] definitely has merit.