Conversations with Excel, part 1


One of our most frequent requirements at work is transporting data between SAS and Excel. For the most part with our ad-hoc reporting the ‘conversation’ is one-way: the extraction and transformation is performed in SAS, then the data is output to a file destination in a format that Excel can read, via ods to csv, html, xml, or tab-delimited text files. The output file can then be opened in Excel and any after-work analysis or presentation performed there – formatting, worksheet formulae, pivot tables and charts can be added.

However, with regular reports it would be nice to automate a lot of this after-work by keeping a template with the formatting, formulae etc already complete, and having SAS simply update the underlying data. This can be done manually via a copy-paste operation from the output file or by using VBA to automate the transfer, and this is perfectly satisfactory – but this is not the only way. A lot of our legacy code uses the very useful but also slightly outdated DDE protocol – I won’t go into the detail of how DDE is used in SAS (usually through a filename statement) but there are plenty of good references available, for example here. DDE allows SAS to control the Excel application via the Excel 4 macro language, to open Excel files and to specify locations within those files to read from or write to. The only real problem we have with DDE is that, as we use SAS Enterprise Guide on the desktop as a thin client and SAS is on the server, all that legacy code is unable to find the Excel application to talk to.

The SAS v9 Excel libname engine (in the SAS/ACCESS package) to a large extent fills the gap left by DDE, without the need to control an instance of the Excel application. The libname statement creates a library out of the Excel workbook, where the tables in the library are the worksheets and named ranges in the workbook. This makes the code relatively simple – for example, I use the following macro to read the range ‘rngData’ from the file ‘fname’ into the SAS dataset ‘fshortname’:

%macro read_file(fname);
*** Libnames ;
* sas dataset directory;
libname sasdata "&path.\&adhocref.\sasdata" ;

* Open file and create libref;
libname returnWB EXCEL "&inputdir.\&fname" USEDATE=NO;

* strip off the file extension;
%let namelen=%eval(%length(&fname)-4);
%let fshortname=%substr(&fname, 1, &namelen);

data sasdata.&fshortname.;
  set returnWB.rngData;
run;

* Close file and clear libname;
libname returnWB clear;

%mend read_file;

In this application, I then use the SAS functions dopen, dnum, dread and dclose to iterate over files in the source directory and create a bunch of SAS datasets from their specified ranges:

%macro get_all_files;

%let fref=flist;

%let dname=%sysfunc(filename(fref, &inputdir));

%let did=%sysfunc(dopen(&fref));

%let numfiles=%sysfunc(dnum(&did));

%do i=1 %to &numfiles;

  %read_file(%sysfunc(dread(&did, &i)));

%end;

%let dname=%sysfunc(dclose(&did));

%mend get_all_files;

The advantage of having a libname created is then that the data can be viewed and manipulated in much the same way as any other SAS dataset. The conversation can of course go the other way, simply by changing the order of the dataset references in the data step:

%macro writeto_file(fname);
*** Libnames ;
* sas dataset directory;
libname sasdata "&path.\&adhocref.\sasdata" ;

* Open file and create libref;
libname returnWB EXCEL "&inputdir.\&fname" USEDATE=NO;

* strip off the file extension;
%let namelen=%eval(%length(&fname)-4);
%let fshortname=%substr(&fname, 1, &namelen);

data returnWB.rngData;
  set sasdata.&fshortname.;
run;

* Close file and clear libname;
libname returnWB clear;

%mend writeto_file;

The macro now creates the range rngData in the workbook (assuming it doesn’t already exist – if it does this method will not work). The article De-Mystifying the SAS Libname Engine in Microsoft Excel contains some great examples of how to access, replace, create and delete ranges in Excel workbooks.

Next time I’ll look at an alternative: SQL pass-through (also through SAS/ACCESS), and the reason this may be preferable.

About these ads
Leave a comment

6 Comments

  1. Geoff,

    Thanks for the excellent post. I appreciate having a real-world example showing how folks can reduce their reliance on DDE. It was a handy hammer in its day, but in the world of distributed computing it’s become a much less effective approach for interoperating with Microsoft Excel.

    I’ve linked to this post from http://blogs.sas.com/sasdummy/index.php?/archives/113-Accessing-Excel-from-SAS-without-DDE.html.

    Chris (I work for SAS, in charge of SAS Enterprise Guide)

    Reply
  2. Thanks for your kind words Chris, and thanks for the link!
    Yes reliance on DDE has become somewhat of an issue where I am. I think the main concern for people is how to replicate DDE in those situations where automation is really required as opposed to just data transfer. Having said that, I believe a lot of those requirements could (and probably should) be delegated to Excel.

    Thanks for dropping by :)

    Reply
  3. Sriram

     /  August 28, 2009

    Hi Geoffness,

    Thanks for the good work and sharing it with all of us. I have a quick question. My data resides on UNIX server and I want to get my final results to be brought to local (windows) excel. Can you please explain me how I declare the “local library” (for near batch mode) on SAS EG?

    Thanks a bunch for your good work.
    -best regards,
    Sriram Bhagavathula

    Reply
    • Hi Sriram

      Where the “local library” can be defined is restricted to the locations that SAS (as opposed to the EG client) can “see” from where it resides – at least as I understand it (someone correct me if I’m wrong). In my workplace, SAS resides on a server, and output from SAS can only be sent to that server.

      This usually isn’t a problem for me as I have a share on that server mapped to a drive letter in Windows locally, so I can open results in Excel from there. However, it sounds like you need to automate the transfer of the spreadsheet from the UNIX server (if this is where SAS lives) to a local directory somewhere, and this could require a separate process (batch script, ftp etc.).

      Good luck, let me know how you get on
      Geoff

      Reply
  1. Conversations with Excel, part 3 « Number Cruncher
  2. Populating Excel from SAS « Number Cruncher

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 202 other followers

%d bloggers like this: