BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GreyHamster
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

6 REPLIES 6
GreyHamster
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
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";

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 597 views
  • 2 likes
  • 2 in conversation