exporting multiple data in a library

[jhhuh]

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

[SASKiwi]

Try this:

PROC SQL; 

  SELECT strip(COMPRESS(MEMNAME)) INTO: Table 

   FROM MEMBERS 

   WHERE OBS=&D; 

  QUIT; 

[LinusH]

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

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

RUN; 

would work as well.

[Peter_C]

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

[Tom · Accepted solution]

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;

[jhhuh]

Awesome!

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

Thanks!!