topic Exporting multiple CSV files from SAS EG local machine in SAS Enterprise Guide

Exporting multiple CSV files from SAS EG local machine

JT1 — Fri, 17 Feb 2017 23:50:18 GMT

Hello,

I installed a DLL file to facilitate the importing/exporting of any files from SAS EG to my local machine and vice versa. Specifically, I'm trying to export mutliple CSV files from a desginated SAS library to different folders on one of my local drives. I developed a macro that successfully builds each of the CSV files needed for the Export Task (I created my own task template...see attached). However, the problem I am encountering is that when I go to run the Export Task, instead of processing all the files, it processes only the last file built from the macro, thereby rendering the macro useless. Is there a way for me to export all files (in this case 4) from the library using the Export Task in one go? I appreciate any help or insight on this. Below is my macro code and I've attached a screen shot of my custom Task Template. Thank you

%macro csv_export (st,aco,datafile);
/* Data to export */
%let lib = TEST;
proc sql noprint;
select memname
into : datafile1 - : datafile4
from sashelp.vmember
where libname = "TEST";
quit;

/* Local folder to download to */
%let download_to = E:\AA_AU_Monthly_Runs\AA_AU 2017\AA_AU_2017_03_Mar\Step_100_Roster_Prep\&st\&aco ;

/* detect proper delim for UNIX vs. Windows */
%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));

%let download_from =
%sysfunc(getoption(work))&delim.&datafile...csv;

filename src "&download_from.";

proc export data=&lib..&datafile.
dbms=csv
file=src
replace;
run;

filename src clear;

%mend csv_export;

%csv_export(TX,ADV,&datafile1);
%csv_export(TX,ADV,&datafile2);
%csv_export(IL,LURIE,&datafile3);
%csv_export(IL,LURIE,&datafile4);

Re: Exporting multiple CSV files from SAS EG local machine

hollandnumerics — Wed, 22 Feb 2017 15:18:54 GMT

JT1,

The problems lies in the following SAS statement:

%let download_from =
%sysfunc(getoption(work))&delim.&datafile...csv;

which points to a single file, and gets overwritten by each macro call. My solution, however, depends on your CSV filenames being the only CSV files present in a &download_from folder:

%let download_from =
%sysfunc(getoption(work));

filename src "&download_from.&delim.&datafile...csv";

You should then be able to use the following text in the Source files box:

&download_from.&delim.*.csv

Hope this makes sense...........Phil

Re: Exporting multiple CSV files from SAS EG local machine

JT1 — Wed, 22 Feb 2017 20:25:11 GMT

Phil,

Thank you for your response! It certainly makes sense and I made your suggested changes. However, when I run the task, I am now receiving the following error:

ERROR: Target folder (&download_to.) does not exist or cannot be accessed on LOKN3DDS (this is the name of my local machine).

I ran it again with my old code and got the same error so I doubt it has anything to do with the modifications but puzzled as to why this error is showing up now and not prior.

-Jared

Re: Exporting multiple CSV files from SAS EG local machine

hollandnumerics — Thu, 23 Feb 2017 12:59:55 GMT

Jt1,

I suspect you are being impacted by macro variable "scope", because, unless you have defined both &download_from and &download_to outside of the macro, it won't exist outside. Therefore I suggest you add a single statement at the top of your program outside of the macro to make sure the macro variables are available everywhere:

%GLOBAL download_from download_to;

..............Phil

Re: Exporting multiple CSV files from SAS EG local machine

JT1 — Tue, 28 Feb 2017 02:50:38 GMT

Hollandnumerics,

I added in the %GLOBAL download_from download_to. However, now I am receiving a different type of error. Specifically....

ERROR: Download exception occurred. <?xml version="1.0" ?><Exceptions><Exception><SASMessage severity="Error">Invalid file, /SASWork/SAS_work064400005256_pwauslsasgap09/SAS_work386F00005256_pwauslsasgap09.</SASMessage></Exception></Exceptions>

Any ideas?

Thanks,

Jared

Re: Exporting multiple CSV files from SAS EG local machine

hollandnumerics — Tue, 28 Feb 2017 10:22:20 GMT

Jared,

I can see 2 copies of the WORK folder (/SASWork/SAS_work064400005256_pwauslsasgap09) in the error message. I would look at what you are actually providing to the EG Task in &download_from and &download_to, because I don't think what you have in &download_from.&delim.*.csv is "folder/*.csv".

.............Phil