I am working on automating our recurring reports. Currently, we run our data through SAS and then output to Excel. Then, we copy and paste the data into our report templates. I would like to get a good book/reference manual that will help me to learn how to use DDE in SAS to do the reporting. Can anyone recommend a good book or tool for this?
I don't know of any books on this subject, and the need for it is somewhat limited. DDE uses a deprecated control set for Excel. More recent Excel versions use a richer command set. There are many papers dealing with DDE and there is a help file published by Microsoft which will provide you with the syntax. The file is ambiguously named MACROFUN.HLP and searching the name on the internet will get you references to SAS Institute and conference papers as well as Microsoft technical papers on the subject.
I had taken a copy of the file a few years back, but can't find it now and have moved to using the version 5 syntax. A number of new functions became available, and my processes became more stable. It is worth the effort to make the change, but as soon as you move outside the strictly SAS environment you need to save your work before running any code, and make sure you reduce the number of applications open at the same time.
I can't pass up the opportunity to recommend ODS as one way to get "formatted" data into Excel, if you mean altering the fonts, colors, headers, etc. When you use ODS, you can get either your data (via proc print) or your other procedure output (via proc report, proc glm, proc reg, etc ,etc) into Excel very simply:
ods html file='c:\temp\ht4.xls' style=sasweb;
ods html3 file='c:\temp\ht3.xls' style=sasweb;
ods msoffice2k file='c:\temp\mso.xls' style=sasweb;
ods tagsets.excelxp file='c:\temp\xp.xls' style=sasweb
The above code is creating either HTML files (HTML, HTML3, MSOFFICE2K) or Spreadsheet Markup Language XML files (TAGSETS.EXCELXP) that Excel knows how to render when it opens the file. Using a file extension of .XLS for the files "fools" the Windows registry into launching Excel when you double click on the file name. ( otherwise, the files would normally have .HTML or .XML file extensions).
If you need to apply Microsoft formats (such as a Microsoft number format or date format) to your data, then with HTML files, you can use the HTMLSTYLE attribute to pass a Microsoft format to Excel. For TAGSETS.EXCELXP, you can use the TAGATTR attribute to pass a Microsoft format to Excel.
One of the hallmarks of the HTML-based methods of getting formatted output from SAS into Excel is that no matter how many procedures you have inside your ODS "sandwich", all the procedure output will be in one worksheet. With the XML-based method of getting formatted output from SAS into Excel, every procedure, by default, starts a new worksheet automatically.
In addition, TAGSETS.EXCELXP has extensive options available to perform formatting inside Excel -- like setting page orientation, controlling column widths, generating frozen headers, turning on autofilters, etc. If you look in the log after submitting the above job, you would see the results of the doc='Help' suboption with all the ExcelXP options to alter formatting.
To open HTML3 files, you need Office 97 or higher; to open HTML4 or Microsoft HTML files, you need Office 2000 or higher; to open Spreadsheet ML files, you need Office 2002/2003 or higher.
Other methods, as David indicated (not SAS methods), involve the use of newer Microsoft technologies like OLE-DB or .COM/.NET applications. In this scenario, SAS becomes the data provider and all the "formatting" is done through your application's interface to Microsoft Excel.