Populating Excel from SAS

A while back, I posted about using the SAS Excel libname engine to read and write to and from Excel files. That turned out to be a reasonably popular topic, and engendered quite a few questions, so I thought I’d follow up with an example of the typical use I’ve made of this facility, along with a couple of lessons learned along the way.

I usually have a pre-formatted template file set up, which I copy to a new file destination and then assign a libname:

data _null_;
    infile "&tmpltdir.\template.xls" recfm=n;  /*Template file*/
    input one_byte $char1. @@;
    file "&rptdir.\report.xls" recfm=n;        /*Report file*/
    put one_byte $char1. @@;
run;

libname wb Excel "&rptdir.\report.xls" ver=2002;

Copying like this (input/put) is a little clunky I think, but it works. X commands are not enabled by default from Enterprise Guide, so this is the best option remaining.

The data will be stored in named ranges set up for that purpose – but before they can be populated, they need to be cleared:

proc datasets library=wb;
  delete Listing Header;
run;
quit;

Listing in this case would be a tabular dataset, and Header is a one row set which contains information like report date, refresh date, titles etc. – any information which is particular to that edition of the report.

The next step is to populate the data in the ranges:

data Header ;
	format startdate enddate refreshdate reportdate date9.;
	progpath="&regref.\sasprogs\&pgmname";
	startdate=&startdate.d;
	enddate=&enddate.d;
	refreshdate="&dwh_d_fmt"d;
	reportdate="&sysdate9"d;
run;

data wb.Header (dblabel=yes);
	set Header;
run;

The code to populate Listing would run much the same way. One point to note here is that sometimes the order of the columns output is important. In this case, it is possible to control the order of columns by either using a retain statement in the data step, or by using proc sql and ordering the columns in the select clause.

Once the ranges have been populated, close the file by clearing the libname:

libname wb clear;

This is important as otherwise the file will be held open by SAS.

And there you have it – the report has now been populated. Actually, there’s a further wrinkle I should mention. For some reason, the report file created often suffers from a peculiar “bloating” effect, where the file size appears to be roughly double what it should be. However, once the file is opened and saved in Excel, the size returns to normal. In a couple of cases involving large numbers of regular reports, I have written a VBA routine to open and save each one in turn…as with the copy step above, I find it a little clunky, but it can save a considerable amount of space.