Shit, totally forgot about
The Path.
I'm not sure if I'd call it transcendental or just plain fucking scary, but it's a great couple (or more, depending on how many characters you play) of hours either way. Plus it's on Steam.
EDIT: Goddamn, I seem to be thinking up new games every 30 seconds.
Anyway,
Sweet Home. One of the few examples of a good licensed game, and maybe the only example where the licensed game is better than it's source (although apparently there is some speculation as to which actually came first, the game or the movie, since they were released on the same day and the movie would have probably taken longer to make), and definitely the most disturbing thing you will experience on the NES, or at least without knowing Japanese.
Hell, if you're going to be playing survival horror, why not start with the original? In fact, when Resident Evil was first proposed, it was going to be a
remake of Sweet Home.
The translated ROM is on
the mainsite.