Really depends on what time of the year you're coming. For me , the best atmosphere , comparable to footy , would be ice hockey. NHL's NYRangers play right in mid-town Manhattan. Tickets are pricey, especially from ticketmaster.. so you'd expect to pay $150 and UP per ticket easy. But if you have never seen a hockey game live , you'll be amazed at the speed and skill of the athletes playing this sport. NHL runs from October to April.
MLB , you got the Yankees and Mets. If you choose one , go with the Yankees. The new stadium is like a baseball museum, but doesen't have the atmosphere as much as the old one. Tickets for baseball are usually much cheaper, just to get in the Stadium. Of course the better the seats the more money. Also there are many trains that go from mid-town up to Yankee Statium. So transportation is easy. MLB runs April through September.
NFL is a bit tricky , because the stadium is actually in New Jersey, also most the games are on Sunday. And if you've never been to an NFL game live, it can seem kinda slow, because all the television breaks and stoppages. I think a 60 minute NFL game only has about 15-20 minutes of real action. Jets tickets would probably be cheaper and more available than the Giants. But it also had alot to do with the opposition. Getting there can be a bit challenging too. NFL goes Sept to Dec.
Never been an NBA fan, so can't offer you much advice here. The Knicks play in the same building as the Rangers. tickets are also really pricey for NBA. NBA and NHL seasons pretty much go on at the same time.
Good Luck and enjoy NYC.