Hello,
I need to export 25 tables into .csv format. Currently they all are SAS datasets and stored in the same location. I know how to export individual tables but now I'd like to do a batch operation and retain tables' names in the final spreadsheets. Your assistance will be greatly appreciated.
Hi DiG,
Here is what you are looking for, the loop will touch all the files in a single library so make sure they all are in a unique library of their own!
Just replace the LIBNAME Path below to the path of your Tables and you are set!
Hope this Helps!
Kind Regards,
Maeshvaran
LIBNAME Test "C:\";
%MACRO Convert2CSV(Libname);
DATA MEMBERS;
SET SASHELP.VMEMBER(WHERE=(LIBNAME = "&Libname"));RETAIN OBS 0;
OBS = OBS+1;KEEP MEMNAME OBS;
RUN;
PROC SQL;
SELECT MIN(OBS) INTO :MIN
FROM MEMBERS;
QUIT;PROC SQL;
SELECT MAX(OBS) INTO :MAX
FROM MEMBERS;
QUIT;%Local D;
%DO D = &MIN %TO &MAX;
PROC SQL;
SELECT COMPRESS(MEMNAME) INTO: Table
FROM MEMBERS
WHERE OBS=&D;QUIT;
PROC EXPORT DBMS=CSV DATA=&Libname..&Table
OUTFILE="C:\&Table..CSV";
RUN;%END;
%MEND;
%Convert2CSV(TEST);
%MACRO OKOK(LOCATION,FILENAME);
ODS CSV FILE=&LOCATION;
PROC PRINT DATA=&FILENAME;
RUN;
ODS CSV CLOSE;
%mend OKOK;
%OKOK("Location\Filename.CSV",Libname.dataset);
Hi hpk,
Thanks for your example. From what I understand LOCATION and FILENAME are code variables that have to be assigned a specific pathname and filename. I don't see where I can do this assignment. Sorry for this confusion, I usually write macros like
%let start = a;
%let end = z;
%do x = &start %to &end;
and then use x in my code. Could you please explain how to assign the actual filenames to FILENAME in your code?
Thank you.
Hi DiG,
I have an answer for you as I do this allot, i will post the code in just a few
Regards,
Maheshvaran
assuming library 'YOUR-LIBRARY' only contains the 25 datasets that you want to export (borrowed Ksharp's code):
proc sql ;
select memname into : a1 - : a25 from dictionary.tables where libname='YOUR-LIBRARY';
quit;
%put _user_;
%macro csv;
%do i=1 %to 25 ;
proc export data=YOUR-LIBRARY.&&a&i outfile="c:\temp\&&a&i...csv " dbms=csv replace;run;
%end;
%mend csv;
%csv
Linlin,
I tried your code several times with minor modifications but it gives me 2 errors:
1. It doesn't recognize my libname although it exists
2. It doesn't recognize the ampersand
Thank you anyway. Meanwhile I'll try Maheshvaran's code.
A minor modification on LinLin's code, in case you don't want to count the number of your tables:
proc sql ;
select memname into : a1 separated by ' ' from dictionary.tables where libname='YOURLIBRARY';
quit;
%macro csv;
%do i=1 %to %sysfunc(countw(&a1)) ;
proc export data=YOURLIBRARY.&&a&i outfile="c:\temp\&&a&i...csv " dbms=csv replace;run;
%end;
%mend csv;
%csv
HTH,
Haikuo
Thank you hai.kuo. I believe your code works too but I already tried Maheshvaran's version and it did the job.
the library in where clause has to be capital letters.
where libname='YOUR-LIBRARY';
Oh, capital letters, that's why it didn't work...
Thank you.
Hi DiG,
Here is what you are looking for, the loop will touch all the files in a single library so make sure they all are in a unique library of their own!
Just replace the LIBNAME Path below to the path of your Tables and you are set!
Hope this Helps!
Kind Regards,
Maeshvaran
LIBNAME Test "C:\";
%MACRO Convert2CSV(Libname);
DATA MEMBERS;
SET SASHELP.VMEMBER(WHERE=(LIBNAME = "&Libname"));RETAIN OBS 0;
OBS = OBS+1;KEEP MEMNAME OBS;
RUN;
PROC SQL;
SELECT MIN(OBS) INTO :MIN
FROM MEMBERS;
QUIT;PROC SQL;
SELECT MAX(OBS) INTO :MAX
FROM MEMBERS;
QUIT;%Local D;
%DO D = &MIN %TO &MAX;
PROC SQL;
SELECT COMPRESS(MEMNAME) INTO: Table
FROM MEMBERS
WHERE OBS=&D;QUIT;
PROC EXPORT DBMS=CSV DATA=&Libname..&Table
OUTFILE="C:\&Table..CSV";
RUN;%END;
%MEND;
%Convert2CSV(TEST);
Maheshvaran, thank you very much - it worked!!
A Pleasure, Enjoy!
I am trying to execute Maheshvarans macro to export 197 sas files. I get the following error regarding line 29: Variable test is not on file sashelp.vmember. Can anyone help me understand why I would be seeing this??
I am trying to execute Maheshvarans macro to export 197 sas files. I get the following error regarding line 29: Variable test is not on file sashelp.vmember. Can anyone help me understand why I would be seeing this??
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.