Desktop productivity for business analysts and programmers

Exporting multiple CSV files from SAS EG local machine

Reply
New Contributor JT1
New Contributor
Posts: 3

Exporting multiple CSV files from SAS EG local machine

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);

 


csv_task_template.PNG
Contributor
Posts: 36

Re: Exporting multiple CSV files from SAS EG local machine

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

New Contributor JT1
New Contributor
Posts: 3

Re: Exporting multiple CSV files from SAS EG local machine

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

 

 

 

Contributor
Posts: 36

Re: Exporting multiple CSV files from SAS EG local machine

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

New Contributor JT1
New Contributor
Posts: 3

Re: Exporting multiple CSV files from SAS EG local machine

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

Contributor
Posts: 36

Re: Exporting multiple CSV files from SAS EG local machine

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

Ask a Question
Discussion stats
  • 5 replies
  • 248 views
  • 0 likes
  • 2 in conversation