topic Re: Upper case/lower case problem when exporting files in SAS Programming

Upper case/lower case problem when exporting files

DiG — Mon, 20 Sep 2021 14:17:50 GMT

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

Re: Upper case/lower case problem when exporting files

PaigeMiller — Mon, 20 Sep 2021 14:38:13 GMT

Try this:

proc export dbms=csv data=&Libname..&Table outfile="C:\mypath\output\%lowcase(&tbl).csv";

Re: Upper case/lower case problem when exporting files

Tom — Mon, 20 Sep 2021 14:57:37 GMT

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;

Re: Upper case/lower case problem when exporting files

DiG — Mon, 20 Sep 2021 16:05:35 GMT

It works! Thank you very much!!

Re: Upper case/lower case problem when exporting files

DiG — Mon, 20 Sep 2021 16:09:05 GMT

Thank you for your comments Tom.
PaigeMiller above proposed a quick&simple solution too.

Re: Upper case/lower case problem when exporting files

Tom — Mon, 20 Sep 2021 17:09:19 GMT

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;