Using quotes in a macro program

[GreyHamster]

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?

[Kurt_Bremser]

What parts of this code need to be dynamic?

[GreyHamster]

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.

[Kurt_Bremser]

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?

[GreyHamster]

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)

[Kurt_Bremser · Accepted solution]

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;

[GreyHamster]

Thanks for the solution, it worked with a little bit of tweaking. It looks a lot cleaner as well.

This line misses a parenthesis: call execute("data &out; set";