How to create "standardized" Excel workbooks using SAS

Question

I have a "wide" SAS data sets that must be exported into a new Excel workbook every week. I want to preserve the column widths and other Excel attributes every week, but I'm having problems getting it to work. Here's what I'm attempting.

1. I used PROC EXPORT to create a new workbook (using sheet="New_TACs").
2. I manually adjusted the column widths and other sheet attributes (like "filters", column widths, wrap, alignment, and "freeze panes").
3. I deleted all the data rows (leaving the first row with the column names) and saved it as a new workbook named "template.xlsx".
4. Using a SAS system call, I copy "template.xlsx" to "this_week.xlsx". I use PROC EXPORT again to try and update the new workbook, but I get warnings. The result contains a sheet named "New_TACS1".

Here is the SAS log:

720  proc export data=new_tacs
721     outfile="\\server-path\this_week.xlsx"
722     replace;
723     sheet='New_TACs';
724  run;

WARNING: The target file may contain unmatched range name and sheet name.
WARNING: The target file may contain unmatched range name and sheet name.
WARNING: File _IMEX_.New_TACs.DATA does not exist.
WARNING: Table _IMEX_."New_TACs" has not been dropped.
NOTE: "New_TACs" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           23.88 seconds
      cpu time            1.80 seconds

I'm at a loss as to what to do and would appreciate any ideas or suggestions.

Answer 1

I think the issue is that with zero rows, SAS isn't properly dealing with the data. I can't get PROC EXPORT to work at all, but with a single dummy row I can at least get it to behave with libname and PROC APPEND. I wouldn't be surprise if the filters are in part responsible for this.

After creating the blank excel file with the SASHELP.CLASS columns, adding a filter, adding one row of dummy data, and saving/closing, I do: (SCANTEXT=NO is mandatory here for update access)

libname newtac "c:\temp\test.xlsx" scantext=no getnames=yes;

proc append base=newtac.'New_TACs$_xlnm#_FilterDatabase'n data=sashelp.class force;
run;

libname newtac clear;

That gets close, at least. I'm getting some blank rows for some reason, perhaps due to other things I did in looking at this.

Your best solution may well be to wait for 9.4 TS1M0 and ODS EXCEL, which will let you do all these things from SAS directly; or to use DDE.

Answer 2

I would recommend checking out SaviCells. http://www.sascommunity.org/wiki/SaviCells. It provides much better SAS to Excel functionality, including creating a template with all your Excel formatting and using that with new data.

Answer 3

Use DDE in SAS to achieve this.

You can create your excel template the way you want it to appear. Using DDE you would then:

1. Open Excel
2. Open the excel file you want to use as the template
3. Populate it with the updated data
4. Save the file as a new filename

It's a bit of an antiquated technology but it gets the job done.

Googling for SAS and DDE will find you plenty of code exmaples and tutorials.