DATA Step, Macro, Functions and more

exporting multiple data in a library

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

exporting multiple data in a library

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


Accepted Solutions
Solution
‎04-15-2013 07:09 PM
Super User
Super User
Posts: 7,039

Re: exporting multiple data in a library

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;

View solution in original post


All Replies
Super User
Posts: 3,252

Re: exporting multiple data in a library

Try this:

PROC SQL; 

  SELECT strip(COMPRESS(MEMNAME)) INTO: Table 

   FROM MEMBERS 

   WHERE OBS=&D; 

  QUIT; 

Super User
Posts: 5,426

Re: exporting multiple data in a library

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

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

RUN; 

would work as well.

Data never sleeps
Valued Guide
Posts: 2,177

Re: exporting multiple data in a library

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

Solution
‎04-15-2013 07:09 PM
Super User
Super User
Posts: 7,039

Re: exporting multiple data in a library

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;

Contributor
Posts: 20

Re: exporting multiple data in a library

Awesome!

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

Thanks!!

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 472 views
  • 0 likes
  • 5 in conversation