Export SAS data as Excel automatically from SAS Server to Local drive

[lk_88]

Hi guys,

Currently I'm using SAS Enterprise Guide 4.3, and I'm trying to export dataset with dynamic dates to Excel from SAS server to my local drive automatically. I have explored the following options but to no avail:

1. ODS Excel does not work;

2. Add-on: Copy files from SAS Server to embed as a step in my project does not work as my dataset name keeps changing because of dynamic dates;

3. proc export - DBMS Excel does not work, and I prefer to use Excel instead of CSV in case the data format changes in my output and it will take time to check.

Hope that someone might have an idea of how to do it, and apologies if it has been answered before.

Thank you!

[Kurt_Bremser]

It's time to upgrade your SAS, then, so that dbms=excel and ods excel become available. EG 4.3 is also very old now, so upgrade that also.

BTW csv is a MUCH more reliable format than Excel. And it doesn't need any license at all.

[lk_88]

Thank you for your reply!

Noted, but my company is using SAS server 9.2, so EG 7.1 will not support it. Hopefully it will be upgraded soon.

Also, can I check whether this is the correct code to export?

/* Data to export */
%let lib  =      work;
%let datafile =  data1;
 
/* Local folder to download to */ 
%let download_to =  C:\folder;
 
/* detect proper delim for UNIX vs. Windows */
%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));
 
%let download_from =
  %sysfunc(getoption(work))&delim.&datafile..xlsx;
 
filename src "&download_from.";
 
proc export data=&lib..&datafile.
  dbms=Excel replace
  file=src
  replace;
run;

filename src clear;

Then I will use the add-on to export out the files since the data can be a macro.

Do let me know if this will work.

Thank you!

[Kurt_Bremser]

Although SAS does not officially support it, EG 7.1 works with a SAS 9.2 backend. We were using it here with SAS 9.2 on AIX before we migrated the server to 9.4.

But that's not your problem. Your problem is 9.2, which should really be upgraded to 9.4 three days before yesterday. Really. I mean it.

All that can of course be avoided by simply using a textual format for data transfer, which is the professional way to go here.

- data can be inspected with a simple text editor

- formats change only when YOU decide it, not when some ***** in Redmond has another brain fart.

- CSV is CSV is CSV, for more than three decades now. I can't count how often I had to deal with inconsistencies in Excel files because of: see above

Since you can't instruct EG to do something (read: copying data) from the SAS server side through code, you need to think of another method to get your data to the desktop. The simplest way would be to mount a network share on the SAS server and write your CSV files to that. Desktops can then read from there. A shared network drive also has the advantage that such resources are usually backed up regularly, giving you more data security.

If your network/server admins don't want to do that, look into filename sftp to write your result files to a location accessible from your desktop(s).

[RW9]

1. ODS Excel does not work;

-- Amazing,I can almost guess why?

2. Add-on: Copy files from SAS Server to embed as a step in my project does not work as my dataset name keeps changing because of dynamic dates;

-- Stop putting data in dataset names.  Dataset names are used in programming, not as a means to convey data.  Put data in the datasets.  Problem solved.

3. proc export - DBMS Excel does not work, and I prefer to use Excel instead of CSV in case the data format changes in my output and it will take time to check.

-- Excel is a really poor format.  Its prevalance in use is due to it simple and flexible front end, however from a programming point of view it is possibly the worst format to store data in.

To answer your question, you could use:

ods tagsets,excelxp

This creates plain text XML which can be read and parsed by Excel, however it is still text XML.