DATA Step, Macro, Functions and more

Export multiple tables from SAS to .csv

Accepted Solution Solved
Reply
Occasional Contributor DiG
Occasional Contributor
Posts: 19
Accepted Solution

Export multiple tables from SAS to .csv

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.


Accepted Solutions
Solution
‎03-09-2012 02:01 PM
Occasional Contributor
Posts: 13

Re: Export multiple tables from SAS to .csv

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

View solution in original post


All Replies
Occasional Contributor hpk
Occasional Contributor
Posts: 5

Export multiple tables from SAS to .csv

%MACRO OKOK(LOCATION,FILENAME);

ODS CSV FILE=&LOCATION;

PROC PRINT DATA=&FILENAME;

RUN;

ODS CSV CLOSE;

%mend OKOK;

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

Occasional Contributor DiG
Occasional Contributor
Posts: 19

Export multiple tables from SAS to .csv

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.

Occasional Contributor
Posts: 13

Export multiple tables from SAS to .csv

Hi DiG,

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

Regards,

Maheshvaran

Super Contributor
Posts: 1,636

Re: Export multiple tables from SAS to .csv

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

Occasional Contributor DiG
Occasional Contributor
Posts: 19

Export multiple tables from SAS to .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.

Respected Advisor
Posts: 3,156

Export multiple tables from SAS to .csv

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

Occasional Contributor DiG
Occasional Contributor
Posts: 19

Export multiple tables from SAS to .csv

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

Super Contributor
Posts: 1,636

Export multiple tables from SAS to .csv

the library in where clause has to be capital letters.

where libname='YOUR-LIBRARY';

Occasional Contributor DiG
Occasional Contributor
Posts: 19

Export multiple tables from SAS to .csv

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

Thank you.

Solution
‎03-09-2012 02:01 PM
Occasional Contributor
Posts: 13

Re: Export multiple tables from SAS to .csv

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

Occasional Contributor DiG
Occasional Contributor
Posts: 19

Export multiple tables from SAS to .csv

Posted in reply to Maheshvaran

Maheshvaran, thank you very much - it worked!! Smiley Happy

Occasional Contributor
Posts: 13

Re: Export multiple tables from SAS to .csv

A Pleasure, Enjoy!

New Contributor
Posts: 2

Re: Export multiple tables from SAS to .csv

Posted in reply to Maheshvaran

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

New Contributor
Posts: 2

Re: Export multiple tables from SAS to .csv

Posted in reply to Maheshvaran

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 21 replies
  • 9868 views
  • 3 likes
  • 10 in conversation