I assume you are talking about residential real estate, as opposed to commercial real estate.
For residential work, the benefits of having a real estate attorney differ depending on whether you are the buyer or seller. As the buyer, a real estate attorney can ensure that all the paperwork is in order, and that all necessary documents to record the title/mortgage in the property are prepared, signed and conveyed appropriately. The attorney can also serve as your advocate, should there be a disagreement at closing as to one or more terms specified in the earlier purchase and sale agreement. Prior to closing, a real estate attorney can negotiate the purchase and sale on your behalf (sometimes important, depending on the condiution of the property, the circumstances, and how contentious the buyer's relationship with the seller is). The attorney also can also provide/coordinate a title search, which is very important IMO.
As an example of the benefit of having an attorney on the buyers side, I recently sold my home in MA. I purchased the home 4 years ago, and hired an attorney to perform a title search to identify any encumbrances on the property. His search identified no encumbrances. However, shortly before we closed on the sale of our home 4 years later, we received a notice from the city indicating that we owed ~$4000 to the town for a lien that was imposed on the property 2 years before we purchased the home, for improvements made to the city roads and sewers. We had title insurance, so I could have gone through the title insurance company to have the lien paid off. But since that looked like a hassle and would likely delay closing, I contacted the attorney who performed our title search. He double checked his records and redid the title search, admitted that he missed the lien, and promptly sent me a check in entire amount of the lien! As a result, the problem was resolved in less than 1 day.
From the sellers side, a real estate attorney provides a slightly different set of benefits. As with buyers, a real estate attorney can negotiate the terms of the P&S, ensure that all documents necessary to convey the property are prepared and signed correctly, and can advocate on the sellers behalf during closing, should their be any disagreement. The attorney can also do a follow up check to ensure that all liens/encumbrances are released from the property, prior to sale. Finally, the sellers attorney can be a nice security blanket at closing, should the buyers have an attorney who decides to make the closing difficult (rare, but it happens).