BookmarkSubscribeRSS Feed
lk_88
Calcite | Level 5

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!

4 REPLIES 4
Kurt_Bremser
Super User

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
Calcite | Level 5

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4383 views
  • 1 like
  • 3 in conversation