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.