Any games that made you feel apart of the environment so much that you forgot you were playing a video game? Or ones that simply sucked you into the setting to such a degree you would react in unexpected ways.
Probably Zelda: The Wind Waker or Majora's Mask for me. Saints Row 2 comes close. Red Dead Redemption too though the UI kinda breaks it. Deadly Premonition does a good job at it with sleep, hunger, gas, etc.
Outside the ps3 I think Silent Hill 2 and Fatal Frame 2 have created the overall most immersive atmosphere. But still, flower is oddly good at this as well.
I think its pretty tough creating atmosphere. It requires subtly that really defies the "fun" nature approach of most games. Hence why you generally only find it in games with horror elements if you ask me.