First: I am sorry for my displayname.
I need to import multiple excel files. So I wrote a program without macro language which works fine.
Now I need to change the variable names with spaces to one without.
I use the code below, which doesn't pass through the macro, probably because of all the quotes.
proc sql;
select
cats("'",cats(name),"'n","=",compress(name,"_","ADK")) as name1
/*%str(cats(%"%'%",cats(name),%"%'n%",%"=%",compress(name,%"_%",%"ADK%"))) as name1*/
into :renamecolumns separated by " "
from
dictionary.columns
where libname="WORK" and
memname="_TEST10"
;
quit;
%put &renamecolumns;
/* Rename the variables */
data _TEST20;
set _TEST10 (rename=(&renamecolumns));
run;
How can I change:
cats("'",cats(name),"'n","=",compress(name,"_","ADK")) as name1
so that it passes through in a macro program?
I would create a macro that runs the import and rename for a single Excel file:
%macro imp_rename(fname=,outdata=);
proc import dbms=xlsx out=_TEMP
datafile= "&fname" replace ;
range="Sheet1$A5:BE2000";
run;
proc sql;
select
cats("'",cats(name),"'n","=",compress(name,"_","ADK")) as name1
into :renamecolumns separated by " "
from
dictionary.columns
where libname="WORK" and
memname=upcase("_TEMP")
;
quit;
data &outdata;
set _TEMP;
rename &renamecolumns;
bestandsnaam = "&fname";
run;
%mend;
Then you can call that macro repeatedly for a list of file and dataset names:
options validvarname=any;
%macro MultImp(dir=,out=);
%let flnm=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&flnm);
* list of filenames;
data list;
length fname $256.;
retain counter 0;
infile myfiles truncover end=done;
input myfiles $100.;
fname=quote(upcase(cats("&dir",'\',myfiles)));
counter + 1;
call execute(cats('%nrstr(%imp_rename(fname=',fname,',outdata=_temp',put(counter,z3.),'))'));
if done then do;
call execute("data &out; set";
do i = 1 to counter;
call execute(' _temp' !! put(i,z3.));
end;
call execute(';run;');
end;
run;
%mend;
What parts of this code need to be dynamic?
It is for multiple excel files, the code isn't dynamic yet, I am just in the process of getting it right.
So it is not really about converting dataset names in macro values, but more about how to pass the expression through a macro to rename the variables.
If you don't have anything that needs to be made dynamic, there is no need for a macro.
So I repeat my question: what parts of this code need to be made dynamic?
Here is the whole macro:
options validvarname=any;
%macro MultImp(dir=,out=);
* dir of filenames;
%let flnm=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&flnm);
* list of filenames;
data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;
fname=quote(upcase(cats("&dir",'\',myfiles)));
call symput('fname_loc',cats("_","&dir",'\',myfiles));
out="&out";
drop myfiles;
* import this range through the list of filenames;
call execute('
* import excels;
proc import dbms=xlsx out=_test10
datafile= '||fname||' replace ;
range="Sheet1$A5:BE2000";
run;
* varnames without spaces or special chars;
proc sql;
select
cats("'",cats(name),"'n","=",compress(name,"_","ADK")) as name1
/*%str(cats(%"%'%",cats(name),%"%'n%",%"=%",compress(name,%"_%",%"ADK%"))) as name1*/
into :renamecolumns separated by " "
from
dictionary.columns
where libname="WORK" and
memname="_TEST10"
;
quit;
%put &renamecolumns;
/* Rename the variables */
data _TEST20;
set _TEST10 (rename=(&renamecolumns));
run;
* add original filename;
data _test20;
set _test20;
bestandsnaam="&fname_loc";
run;
*add all;
data '||out||';
set '||out||' _test;
run;
proc delete data=_test; run;
');
run;
filename myfiles clear;
%mend;
%multimp(dir=d:\excel,out=out_100)
I would create a macro that runs the import and rename for a single Excel file:
%macro imp_rename(fname=,outdata=);
proc import dbms=xlsx out=_TEMP
datafile= "&fname" replace ;
range="Sheet1$A5:BE2000";
run;
proc sql;
select
cats("'",cats(name),"'n","=",compress(name,"_","ADK")) as name1
into :renamecolumns separated by " "
from
dictionary.columns
where libname="WORK" and
memname=upcase("_TEMP")
;
quit;
data &outdata;
set _TEMP;
rename &renamecolumns;
bestandsnaam = "&fname";
run;
%mend;
Then you can call that macro repeatedly for a list of file and dataset names:
options validvarname=any;
%macro MultImp(dir=,out=);
%let flnm=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&flnm);
* list of filenames;
data list;
length fname $256.;
retain counter 0;
infile myfiles truncover end=done;
input myfiles $100.;
fname=quote(upcase(cats("&dir",'\',myfiles)));
counter + 1;
call execute(cats('%nrstr(%imp_rename(fname=',fname,',outdata=_temp',put(counter,z3.),'))'));
if done then do;
call execute("data &out; set";
do i = 1 to counter;
call execute(' _temp' !! put(i,z3.));
end;
call execute(';run;');
end;
run;
%mend;
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.