topic Re: exporting multiple data in a library in SAS Programming

exporting multiple data in a library

jhhuh — Sun, 14 Apr 2013 21:32:39 GMT

Hi all,

I found an SAS code which can export multiple sas files in a library.

Even though the code generally works fine, but there is a minor issue,

The name of exported files have trailing blanks

(such as.. when the name of sas data is "datatobeused", the name of exported file is "datatobeused .csv" ),

so I need to clean them before I use those files with other statistical packages.

But the problem is that there are more than hundreds files that I need to clean... I hesitate to do it..

I would like to find a problem with the code and use it.

Can anyone find which part should be corrected?

Thanks in advance!

LIBNAME Test "/home/usr/jh/a/";

%MACRO Convert2DTA(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="/home/usr/jh/b/&Table..csv";

RUN;

%END;

%MEND;

%Convert2DTA(TEST);

Re: exporting multiple data in a library

SASKiwi — Mon, 15 Apr 2013 05:25:40 GMT

Try this:

PROC SQL;

SELECT strip(COMPRESS(MEMNAME)) INTO: Table

FROM MEMBERS

WHERE OBS=&D;

QUIT;

Re: exporting multiple data in a library

LinusH — Mon, 15 Apr 2013 08:09:15 GMT

PROC EXPORT DBMS=csv DATA=&Libname..&Table

OUTFILE="/home/usr/jh/b/%cmpres(&Table).csv";

RUN;

would work as well.

Re: exporting multiple data in a library

Peter_C — Mon, 15 Apr 2013 22:15:47 GMT

dio you want us to fix the problem that creates the trailing balnks, or rename the CSV files after they are named like that?

Re: exporting multiple data in a library

Tom — Mon, 15 Apr 2013 23:09:41 GMT

The spaces are being created because of the way that you a putting the member name into the macro variable using PROC SQL. Add the SEPARATED BY clause and the trailing spaces will not be generated. In SAS 9.3 there is an option to trim the spaces without using separated by. You should also add the NOPRINT option to prevent PROC SQL from generating a page of output for every member name you pull from the dataset.

PROC SQL NOPRINT ;

SELECT MEMNAME INTO :Table SEPARATED BY ' '

FROM MEMBERS

WHERE OBS=&D;

QUIT;

You can also easily remove the trailing (and leading) spaces from a macro variable by just assigning it back to itself.

%LET TABLE=&TABLE;

Re: exporting multiple data in a library

jhhuh — Tue, 16 Apr 2013 04:08:30 GMT

Awesome!

Thank you very much! I learn lots of things about SAS every time I ask for help here!

Thanks!!