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);
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;
Try this:
PROC SQL;
SELECT strip(COMPRESS(MEMNAME)) INTO: Table
FROM MEMBERS
WHERE OBS=&D;
QUIT;
PROC EXPORT DBMS=csv DATA=&Libname..&Table
OUTFILE="/home/usr/jh/b/%cmpres(&Table).csv";
RUN;
would work as well.
dio you want us to fix the problem that creates the trailing balnks, or rename the CSV files after they are named like that?
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;
Awesome!
Thank you very much! I learn lots of things about SAS every time I ask for help here!
Thanks!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.