topic Macro doesn't update variables after argument parsing in do loop in New SAS User

Macro doesn't update variables after argument parsing in do loop

pkopersk — Thu, 19 Sep 2019 10:48:27 GMT

Hi, I have issues with running a macro in a do loop.

I have the following macro which works on its own:

%macro import_excel(path,year,month, sheetname);
%put &path.\CRT&year.\&month.&year..xlsx;

proc import out=CRT_&year._&month.
datafile="&path.\CRT&year.\&month.&year..xlsx"
dbms = xlsx replace; 
sheet="&sheetname.";

data CRT_&year._&month.; 
set CRT_&year._&month.;
/* If numeric imported as character, then convert to numeric*/
array chars {*} _character_;

do _n_ = 1 to dim(chars);

   chars{_n_} = put(chars{_n_},BEST18.);

end;
year = "&year.";
month = "&month.";

run;
%mend;

Then,

I try to iterate over years and months like this:

%import_excel(C:\Users\a0782369\Desktop\testluk, 2018,janvier,RawData);
%import_excel(C:\Users\a0782369\Desktop\testluk, 2018,février,RawData);
%import_excel(C:\Users\a0782369\Desktop\testluk, 2019,janvier,RawData);
%import_excel(C:\Users\a0782369\Desktop\testluk, 2019,février,RawData);

It does work without problems. However I am looking for a more elegant solution:

data _NULL_;
length m $20;
do yrs = 2018 to 2019;
	call symput('i',yrs);
	do mth = 'janvier', 'février', 'mars', 'avril', 'mai', 'juin', 'juillet', 'août', 'septembre', 'octobre', 'novembre','décembre' ;
	call symput('m',mth);
		%import_excel(C:\Users\a0782369\Desktop\testluk, '&i.', '&m.' ,RawData));
	end;
end;
run;

However, it does not update the datafile="&path.\CRT&year.\&month.&year..xlsx" at all and nothing works afterwards.

Thank you.

Re: Macro doesn't update variables after argument parsing in do loop

Kurt_Bremser — Thu, 19 Sep 2019 11:32:47 GMT

First of all, you have invalid quote symbols in your code, probably caused by using improper editing software (do NOT use word processors for code).

Second, and more important, the macro trigger is resolved when the data step is compiled, so anything the data step does when it executes is irrelevant. Use call execute() if you want dynamic execution of macro calls:

call execute(cats('%nrstr(%import_excel(C:\Users\a0782369\Desktop\testluk,',yrs,',',mth,',',RawData,'))'));

Re: Macro doesn't update variables after argument parsing in do loop

FreelanceReinh — Thu, 19 Sep 2019 11:59:56 GMT

Hi @pkopersk,

In addition to the issue with the macro calls resolving to invalid syntax (like "PROC step in a DATA step"), as pointed out by Kurt Bremser, your LENGTH statement should use the DATA step variable MTH, not the macro variable M in order to avoid truncation of the month names (longer than "janvier"). Personally, I would avoid national language characters in SAS names (my SAS version didn't even tolerate the accented characters in dataset names). You can use the BASECHAR function for an easy conversion:

data _null_;
length mth $9;
do yrs = 2018 to 2019;
  do mth = 'janvier', 'février', 'mars', 'avril', 'mai', 'juin', 'juillet', 'août', 'septembre', 'octobre', 'novembre','décembre';
    call execute(catx(',','%nrstr(%import_excel(C:\Users\a0782369\Desktop\testluk',yrs,basechar(mth),'RawData))'));
  end;
end;
run;