Lets be straight about this. In New York, being a private pilot of limited resources can be a bit of an exercise in frustration. Owning a plane is out of the question — it will run you over 50k for an old single engine, and another 10k+ per year in ownership costs. Rental costs are not much cheaper. Once you throw in fuel, landing fees, and insurance, a weekend away will run you more than double the price of a first class commercial flight.
Don’t think about the destination. Think about the journey. The process of learning to fly can be immensely satisfying. It’s hard to put a price on the sense of wonder and achievement that a private pilot course can endow. When you consider the fact that a discovery flight can be had for as cheap as $50 and that you can get to the flight school via public transport, suddenly the math starts to make sense.
So you want to do it — take one of those little birds up for a whirl, give it a little of the ol' Yeagar treatment, bounce around the clouds for a bit, then settle in for a nice clean 3 point landing. Alright. We'll leave it to the instructor to puncture those dreams, but we'll deflate them a bit.
A $50 “discovery” flight consists of about 30 minutes of flight time and takes place in the cheapest slowest Cessna trainer they've got. You're going to have exactly enough time to take off (the instructor will do that), fly in a couple of circles (that's your bit), and then line up and land (his bit again). Don't get me wrong — it'll be a blast and you'll be telling the story for years, but it's important to have your expectations in line.
If you don't own a car your best bet is going to be either Teterboro or Linden airport. Teterboro is pretty high traffic and might be a bit intimidating for a first time flight, but it has the advantage of being immediately accessible from Jersey transit. Linden, on the other hand, is a bit calmer with shorter take off times, but will require a very short cab ride ($6) from the Path train.
New York City Flight Training Schools
Air Fleet Training - Schools in both Linden and Teterboro
Million Air Flight Training - Teterboro, introductory lesson only $75
Panorama Flight Serice - Westchester County Airport, White Plains
Pegasus Global Helicopters - Helicopters aren't cheap to fly, but $199 for an hour long lesson looks pretty good when you compare it to $80/pp to be packed in with 8 other people for a helicopter site seeing flight.