I usually stay out of OT, but too many good memories in this thread.
(Old) Consoles:
Ninja Gaiden on the NES was very hard. Not a lot of extra lives available, and some of the levels are extremely tricky. The last couple bosses are pretty torturous as well. I did beat this back in the day, but it took months. Maybe the top of my list in terms of frustration, but you always felt like you were *that* close to making it through.
Castlevania I don't recall being *that* hard (it was hard, but not impossible); I seem to recall spamming holy water against a lot of later bosses FTW.
Ditto for Kid Icarus; it's a fairly long game, but beatable. If they didn't have passwords, it would be very tough. With passwords, it's doable.
Mega Man 1 is insanely hard. It's the only Mega Man game I can't breeze through. The later ones got a *lot* easier, along with the Megaman X series (still a lot of fun, just not impossibly hard).
Battletoads (both on the NES and SNES) was quite tough to beat. You have to get *really* good at the jetbike levels to not burn all your lives/continues there, and the only way to learn the timing is trial and error (and it's halfway through the game, so it takes you 20 minutes to get back there if you run out of continues ). I'm not sure I beat the NES one until I played it on an emulator and was able to save/load images.
(Mike Tyson's) Punch-Out on the NES is also very hard, but boils down to learning the patterns and timing for each fighter. Passwords make the game much less frustrating than it otherwise would have been.
Blaster Master I don't think I ever beat. I definitely got the wall-climbing ability (which is pretty far along), but I don't think I ever got all the way through.
A couple people mentioned newer games:
Ikaruga isn't that hard to *beat* -- but getting A (or S, or whatever the top one is) ranks on all the levels at arcade difficulty is probably one of the hardest video game challenges available. You have to be incredibly fast and precise, and never die (since it messes up your chains and you won't get enough points). Not dying against some of the later bosses while killing them in under 30 seconds is *really* hard.
The hardest 'new' game I have played has to be Devil May Cry 3 on the PS2. I made it through (eventually!) on the 'easy' difficulty setting, but I generally died very quickly on even 'normal' (which, to be fair, was the 'hard' mode in the original Japanese release). I have some healthy respect for the hand-eye coordination of anyone who can beat it on 'Dante Must Die!'. I don't have an XBox, though, so I haven't played the new Ninja Gaiden all the way through (I did try it at my friend's house, and I died. A lot.)
In terms of sheer frustration, trying to get gold on all the medals in any Gran Turismo game is way up there on my list (I've still never gotten all golds in any of them, and just getting all the licenses is a bitch).
Anybody try to get all the cheats/medals in Goldeneye on the N64 (or to get all the medals in any of the Timesplitter games)? I never managed to get the Invulnerability cheat in Goldeneye, which required you beating the second level in some insanely low amount of time (like 60 seconds or something). VERY frustrating.
Arcade:
I recently picked up Capcom Classics Collection on the PS2, which has (among many others) Ghosts&Goblins, Ghouls&Ghosts, and Super Ghouls&Ghosts (so I've played them all fairly recently). All are incredibly difficult. Unlimited continues in the repackaged version helps a lot.
Several people mentioned not finishing Rampage -- I'm almost certain the original arcade and NES versions just keep going forever.
The first Dragon's Lair was very frustrating, since you only rarely got hints about which way to go (so it devolved into a lot of trial and error until you memorized all the scenes). Dragon's Lair 2 and Space Ace were more manageable (though still difficult). You can actually get these on DVD now!
Marble Madness was, indeed, very frustrating. I never beat it in the arcade. I got through it in the Midway Arcade Classics collection, though. In that same collection are a number of VERY hard Midway arcade games (like Smash TV, Total Carnage, NARC, RoadBlasters, and Robotron:2084).
A number of people mentioned text/graphical adventure games that were annoyingly vague (Myst) or required you to make logical leaps that most people won't figure out (like some of the old Infocom text adventures, especially Hitchhiker's if you haven't read the books). This is almost a different category than most of the other action-oriented games.