SAS Integration with MS Office

There has been interesting developments in the integration of SAS and Microsoft Office. I spoke to Vincent DelGobbo who is a Senior Systems Developer within the Web Tools Group of SAS Institute. He recognizes that users commonly have Microsoft Excel on their desktop and they use it to review their data. SAS is therefore trying creates integration that makes it easy to transfer data between SAS and MS Office. The new development is possible with the latest version of Office XP. Microsoft is updating its file format to XML. This open file format allows SAS to then generate files in which MS Excel can read directly and gain functionality such as having each dataset placed into individual work sheets within an workbook. The examples which Vincent showed will work with SAS 9.1 and MS Office XP service pack 3.

This is accomplished through ODS. SAS implementation will allow users to use their existing procedures such as PROC REPORT or PRINT or TABULATE. The only difference in sending it to Excel or Word is the ODS options. Here is an example:

ods listing close;
ods tagsets.MSOffice2K style=Banker file='aedata.htm';

   title; footnote;

   proc print data=pharma.phcae noobs label;
      by protocol;
      var patient visit aedate aecode aetext aesev aesevc frequency;
   run; quit;

   proc tabulate data=pharma.phcae;
      by protocol;
      var aesev;
      class aetext aesevc;
      table aetext*aesevc,aesev*pctn;
      keyword all pctn;
      keylabel pctn='Percent';
   run; quit;

ods tagsets.MSOffice2K close;

The main difference here is the tagset being set to MSOffice2K. This is an HTM file that can be read into MSWord which will handle Word tables. Since the development of the XML tagsets from Microsoft is not yet final, some of the features are still considered experimental. Vincent tells me that things may be changing and that SAS will continue to implement features as Microsoft publishes their XML schema for office. However, the initial release for Excel and Word is available in SAS 9.1.

The example for Excel would be:

ods listing close;
ods tagsets.ExcelXP style=Banker file='aedata.xml';
   * PROC PRINT and PROC TABULATE code here;
ods tagsets.ExcelXP close;

This will produce a file that Excel can read directly. There are some color limitations with Excel but SAS will produce the output as it would before. There has been some development to assist the import of the new Excel XP into SAS as well. This is accomplished through a macro that is available.

filename myxml URL 'http://Web-server/mydata.xml';
   %xlxp2sas(excelfile=FILEREF:myxml,
   mapfile=excelxp.map);

By default, the release of SAS 9.1 has many tools that would all users to produce and import files directly with XML file structure for MSOffice. There are some formatting limitations but users can customize through ODS PROC TEMPLATE to fine tune their output. SAS integration with Office will be a welcome move for many of us who use Excel and Word.