DDE links alternative

[deleted_user]

Hi,

Right now in my company we are using SAS 8.2 and the Base SAS, SAS/Connect to do the processing. We connect to the server using SAS/Connect and then use the Proc download to get the data and then disconnect from the server and then do the processing and then use DDE links to write data in to excel with formatting and then they are uploading it to the server using proc upload.

Right now we have SAS 9.1.3 and SAS Enterprise Guide also. Is there any other way to write to excel with formatting other than DDE that we can do using SAS EG.

Please guide.

[Cynthia_sas]

Hi:
If you want nice formatting (colors and fonts), you can use either:
1) HTML-based methods: ODS HTML, ODS HTML3, ODS MSOFFICE2K all of which create HTML files that Excel can open. However, with HTML-based methods, you are limited to your output being put in one worksheet in one workbook, because Excel treates 1 HTML file as 1 Worksheet.

2) XML-based methods: Starting in Office 2002/2003, Microsoft announced the specification for Spreadsheet Markup Language XML. SAS and ODS can create that type of XML file with the ODS TAGSETS.EXCELXP destination. One advantage of this method is that you can create multiple worksheets in one workbook using TAGSETS.EXCELXP.

There have been lots of forum postings about using both of these methods. If you search though the forums or the support.sas.com web site, you can find lots of examples. Here's a place to start:
http://support.sas.com/rnd/base/ods/templateFAQ/Excel1.pdf

This PDF file discusses the 2 basic methods outlined above and some advanced methods, too (such as using SAS/IntrNet, etc).


cynthia

[deleted_user]

Hi,

Thanks for the support.

I have to use Excel 2007 and can not use HTML. Need to create direct excel itself.
any suggestion for this? and how i can do this using SAS enterprise Guide.

Thanks again.

[Cynthia_sas]

Hi:
From within EG, you can EXPORT your data or report to Excel, however, I believe there is no formatting with this option.

Spreadsheet Markup Language XML -IS- a Microsoft standard. It is the description of how a multi-sheet workbook should be rendered in Excel with colors and fonts and other formatting (like $ ) applied. Even though Spreadsheet Markup Language was an Office 2003 definition, Office 2007 will open and render the files correctly if they have Spreadsheet Markup Language tags. ODS TAGSETS.EXCELXP creates Spreadsheet Markup Language XML that conforms to this Microsoft specification.

This Microsoft site discusses the advantages/disadvantages of HTML versus XML:
http://msdn.microsoft.com/en-us/library/aa140062(office.10).aspx

The new Office 2007 .XLSX files are a new form of XML in which the pieces of the Workbook are stored in a zip package/XML format. The Office 2003 XML is not stored in this format. However it is still a valid Excel format and under File --> Save AS, the file type is: XML Spreadsheet 2003 (*.xml) also, under File --> Open, you can open an XML file that conforms to the XML Spreadsheet 2003 specification - -such as files created by ODS TAGSETS.EXCELXP.

XML is -not- HTML. Even Microsoft says so. I don't know of any other alternatives except those mentioned -- using the Export or Libname engine functionality creates a binary Excel workbook -- but with no formatting. Using TAGSETS.EXCELXP creates an XML Spreadsheet 2003 workbook -- with formatting.

cynthia