BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Maheshvaran
Calcite | Level 5

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

21 REPLIES 21
hpk
Calcite | Level 5 hpk
Calcite | Level 5

%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
Fluorite | Level 6 DiG
Fluorite | Level 6

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
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

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
Fluorite | Level 6 DiG
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6 DiG
Fluorite | Level 6

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

Linlin
Lapis Lazuli | Level 10

the library in where clause has to be capital letters.

where libname='YOUR-LIBRARY';

DiG
Fluorite | Level 6 DiG
Fluorite | Level 6

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

Thank you.

Maheshvaran
Calcite | Level 5

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
Fluorite | Level 6 DiG
Fluorite | Level 6

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

Maheshvaran
Calcite | Level 5

A Pleasure, Enjoy!

Alicia33
Calcite | Level 5

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 21017 views
  • 4 likes
  • 10 in conversation