You can do this with a workaround...
First you want to replace your standard SMS notification with one from Tasker:
1. In your SMS app disable notifications
2. Set up a profile in Tasker based on event - recieved text. perform action Notify Sound followed by Notify LED (depending on if you want the LED to blink of not) use the title %SMSRN and Text %SMSRB in both notifications. The effect of this that the LED notification will replace the sound notification so you will only have one notification in your statusbar. Tasker has no action for a combined LED and sound notification. (the replacement happens almost seamlessly) Note: the LED will only blink if the screen is off.
3. Add to the end of whatever task you have a Notify Cancel task with the title %SMSRN.
4. Add a profile based on event UI Notification Click and a task to open your SMS app (Or whatever you want to happen when clicking the notification)
I used the app Zedge from the market to find notification tones, you can download them in the app and use a file manager to move them to a location of your choosing. after that you can uninstall Zedge if you don't want it.
This will not mark the SMS read in your SMS app but it will at least clear the notification bar.
I'm sure you can make it fancy and show number of unread SMS in the icon etc but I haven't looked into it. I'm really anal about keeping my notification bar clean so i rarely have more than one unread SMS. (Judging from your question I'm guessing you are too :) )
Hope this helps