Good day!
I am facing a small problem in my SAS code that I can't figure out. The macro below exports all SAS datasets in library MYLIB into csv files. It works seamlessly but the output csv file names come out all uppercase. I need them to be lowercase. Could you please tell me how to achieve it?
Thank you.
Libname MYLIB "C:\mypath";
%MACRO Convert2CSV(LIBNAME); /*do not change it!*/
data members;
set sashelp.vmember(where=(LIBNAME = "&Libname")); /*do not change it!*/
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;
%let tbl = %trim(&Table);
proc export dbms=csv data=&Libname..&Table
outfile="C:\mypath\output\&tbl..csv";
run;
%end;
%mend;
%Convert2CSV(MYLIB);
Try this:
proc export dbms=csv data=&Libname..&Table outfile="C:\mypath\output\%lowcase(&tbl).csv";
Try this:
proc export dbms=csv data=&Libname..&Table outfile="C:\mypath\output\%lowcase(&tbl).csv";
Just store the lowcase name into the macro variable.
You don't want to run COMPRESS() on the name. If you don't want the macro variable to have trailing spaces use the TRIMMED keyword. If you are using VALIDMEMNAME=EXTEND then you might want to use NLITERAL() for the TABLE macro variable.
proc sql noprint;
select nliteral(memname), lowcase(memname)
into :Table , :tbl trimmed
from members
where obs=&d
;
quit;
proc export dbms=csv data=&Libname..&Table
outfile="C:\mypath\output\&tbl..csv" replace
;
run;
Note that adding a patch, %lowcase(), on top of a patch , %trim(), on top of a patch, compress(), is going to make your code harder to understand and debug.
You could also just get rid of all of the macro logic. It is much easier to debug SAS code instead of macro code.
%macro Convert2CSV(LIBNAME);
data members;
set sashelp.vmember(where=(LIBNAME = %upcase("&Libname"));
obs+1;
length filename $200;
filename=quote(cats('C:\mypath\output\',lowcase(memname),'.csv'));
call execute(catx(' '
,'proc export dbms=csv data=',catx('.',libname,nliteral(table))
,'outfile=',filename,';run;'
));
keep obs libname memname filename;
run;
%mend Convert2CSV;
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.