Plus, how many times can you sail up and down the Hudson. Beautiful scenery but it gets boring after awhile.
I agree with that for sure, but you're going to have the same issue at Sheepshead. Out to the ocean, get beat up a little bit, then back home, with less scenery. At least at 79th, you can easily sail down to the upper harbor where the sailing and scenery are great, though there is a fair bit of traffic.
The concept of having places to go and things to do is an issue you should consider. I get the concept of wanting ocean experience, but you're not really going to get that by doing day sails from Sheepshead Bay. You are going to need to do passages for that, even if they are just one or two nighters at first. Keeping the boat in Sheepshead is not going to make much of a difference for that kind of thing, IMHO.
If it were me, and these things are entirely personal preference and subjective, I'd pick a spot that offers more in the way of easy access to good places to go. For your (our) area, that really is Long Island Sound. I know you say you've had a boat at City Island, so I presume you know the pluses and minuses of the western Sound, but perhaps consider something out east (Sag Harbor, Three Mile Harbor, Conn. River, Mysic/Noank). It's very pretty, the wind is great, plenty of places to go for great weekend and week trips, and it gives you relatively easy access to the ocean, depending on where you choose to keep the boat. The negative of course is that you would need to drive the better part of two hours to get to the boat from the city. Just food for thought.