Export multiple tables from SAS to .csv

[DiG]

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.

[hpk]

%MACRO OKOK(LOCATION,FILENAME);

ODS CSV FILE=&LOCATION;

PROC PRINT DATA=&FILENAME;

RUN;

ODS CSV CLOSE;

%mend OKOK;

%OKOK("Location\Filename.CSV",Libname.dataset);

[DiG]

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.

[Maheshvaran]

Hi DiG,

I have an answer for you as I do this allot, i will post the code in just a few

Regards,

Maheshvaran

[Linlin]

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

[DiG]

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.

[Haikuo]

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

[DiG]

Thank you hai.kuo. I believe your code works too but I already tried Maheshvaran's version and it did the job.

[Linlin]

the library in where clause has to be capital letters.

where libname='YOUR-LIBRARY';

[DiG]

Oh, capital letters, that's why it didn't work...

Thank you.

[Maheshvaran · Accepted solution]

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

[DiG]

Maheshvaran, thank you very much - it worked!!

[Maheshvaran]

A Pleasure, Enjoy!

[Alicia33]

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??

[Alicia33]

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??