topic Re: Export multiple tables from SAS to .csv in SAS Programming

Export multiple tables from SAS to .csv

DiG — Fri, 09 Mar 2012 16:46:32 GMT

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.

Export multiple tables from SAS to .csv

hpk — Fri, 09 Mar 2012 18:05:14 GMT

%MACRO OKOK(LOCATION,FILENAME);

ODS CSV FILE=&LOCATION;

PROC PRINT DATA=&FILENAME;

RUN;

ODS CSV CLOSE;

%mend OKOK;

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

Export multiple tables from SAS to .csv

DiG — Fri, 09 Mar 2012 18:29:35 GMT

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.

Export multiple tables from SAS to .csv

Maheshvaran — Fri, 09 Mar 2012 18:40:05 GMT

Hi DiG,

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

Regards,

Maheshvaran

Re: Export multiple tables from SAS to .csv

Linlin — Fri, 09 Mar 2012 18:55:56 GMT

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

Re: Export multiple tables from SAS to .csv

Maheshvaran — Fri, 09 Mar 2012 19:01:28 GMT

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

Export multiple tables from SAS to .csv

DiG — Fri, 09 Mar 2012 19:28:15 GMT

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.

Export multiple tables from SAS to .csv

Linlin — Fri, 09 Mar 2012 19:32:40 GMT

the library in where clause has to be capital letters.

where libname='YOUR-LIBRARY';

Export multiple tables from SAS to .csv

Haikuo — Fri, 09 Mar 2012 19:43:35 GMT

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

Export multiple tables from SAS to .csv

DiG — Fri, 09 Mar 2012 19:44:57 GMT

Maheshvaran, thank you very much - it worked!!

Export multiple tables from SAS to .csv

DiG — Fri, 09 Mar 2012 19:47:12 GMT

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

Thank you.

Export multiple tables from SAS to .csv

DiG — Fri, 09 Mar 2012 19:50:00 GMT

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

Re: Export multiple tables from SAS to .csv

Maheshvaran — Fri, 09 Mar 2012 20:01:32 GMT

A Pleasure, Enjoy!

Re: Export multiple tables from SAS to .csv

N311V — Thu, 28 Feb 2013 02:29:16 GMT

Hi All,

I've got Maheshvaran's script successfully running but all the the files have some problematic trailing blanks. Looks as though it's totaling 32 characters so if my Table name only has 10 characters there will be 32 blanks.

I'm a little new to SAS so I'm having trouble figuring out how to get rid of the blanks. I assume I could use something a long the lines of myfile=trim(right(Table)) and then do OUTFILE="&myfile..csv" but I don't know how to create this myfile variable in SAS, or if I'm even on the right track.

Any help would be appreciated.

Re: Export multiple tables from SAS to .csv

Tom — Thu, 28 Feb 2013 02:34:17 GMT

Side effect of using SQL to create the macro variable.

Change this line:

SELECT COMPRESS(MEMNAME) INTO: Table

select memname into :table separated by ' '

or add this statement before the PROC EXPORT

%let table=&table ;

Re: Export multiple tables from SAS to .csv

N311V — Thu, 28 Feb 2013 02:39:57 GMT

Oh wow that was fast!

And either option does the job.

Thanks heaps Tom.

Re: Export multiple tables from SAS to .csv

kbk — Tue, 19 Mar 2013 19:26:53 GMT

I don't think Hai.Kou's suggestion actually works because the space-delimited string of tables to export was not being scanned to pick out individual datasets. I've added %scan(&a1, &i, ' ').

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.%scan(&a1, &i, ' ')

outfile="c:\temp\%scan(&a1, &i, ' ').csv "

dbms=csv

replace;

run;

%end;

%mend csv;

%csv

Re: Export multiple tables from SAS to .csv

Alicia33 — Wed, 26 Feb 2014 14:46:42 GMT

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

Re: Export multiple tables from SAS to .csv

Alicia33 — Wed, 26 Feb 2014 14:47:46 GMT

Re: Export multiple tables from SAS to .csv

EnricoB — Wed, 02 Apr 2014 17:42:39 GMT

Hi Tom

do you know why the following code to export an entire library of datasets into csv files is not working properly:

I mean when there is a cell with two lines of comments ex: 001 I have a pen.

The pen is on the table.

002 xxxxx

exporting with the command of Guide enterprise tha two lines are exported in the right way leaving 1 line for 1 code 001 I have a pen. The pen is on the table

002 xxxxxx

The almost right code is:

data _null_;

set sashelp.vmember;

where libname eq 'BISCOE';

where also memtype eq 'DATA';

length export $256;

*Insert the path (NO CASE SENSITIVE)with the name given in the server of SAS, below!!;

export = catx(' ','proc export data=',catx('.',libname,memname),'outfile=',quote(cats('f:\accesstopcfiles\enrico\',memname,'.CSV')),' dbms=csv replace;

delimiter=","; run;');

put export;

call execute(strip(export));

run;