topic Re: Dynamically call macro for different variables in SAS Programming

Dynamically call macro for different variables

Deyanamika — Tue, 12 Apr 2022 18:39:58 GMT

I want to run a macro for 60 variables...

/* step 1 : made a variable list */
Proc sql;
Select distinct var_name into : var_list separated by ' ' from have;
quit;

/* Step 2 : defined the Marco */
%macro na_update(var);
Data want;
set have;
&Var = tranwrd(&var, "NA", '0');
If &var="." then do &var = '0';
end;
run;
%mend;

/* Runs fine if i run step 2 separately for single variable */

/* step 3 : call macro dynamically*/

%local i, next_var;
%let i=1 ;
%do %while (%scan (&var_list, &i);
%let next_var = %scan(&var_list, &i);
%na_update(& next_val);
%let i = %eval(&i +1);

%end;
%mend;

%macro_call();

The code is running fine if i run step 2 for single variable. On running the whole code, I get no errors but no desired output... Log looks good and values are assigned rightly... Not been able to understand the problem...
Thanks in advance!

Re: Dynamically call macro for different variables

PeterClemmensen — Tue, 12 Apr 2022 18:44:53 GMT

You want to run the macro %na_update for each value of var_name in the have data set, correct?

Re: Dynamically call macro for different variables

PaigeMiller — Tue, 12 Apr 2022 18:47:39 GMT

%na_update(& next_val);

The error is a typographical error, you have misspelled the macro variable name.

Re: Dynamically call macro for different variables

Deyanamika — Tue, 12 Apr 2022 18:46:24 GMT

Yes, right. That's what I am looking for.

Re: Dynamically call macro for different variables

PeterClemmensen — Tue, 12 Apr 2022 18:55:59 GMT

Ok. I'm not sure what you're aiming for here. The purpose of the macro is unclear to me.

However, speaking of the overall logic of you requirement, I would go about it differently. Instead of relying on the macro facility to generate the series of macro calls, I would use the Call Execute Routine and do something like this.

Obviously, this is untested, since I can't see your data.

/* Step 1 : Define the macro */
%macro na_update(var);
Data want;
set have;
&Var = tranwrd(&var, "NA", '0');
If &var="." then do &var = '0';
end;
run;
%mend;

/* Step 2 : Use Call Execute logic to call the macro for each value of var_name in have */
data _null_;
   set have;
   call execute('%nrstr(%na_update('||var_name||'))');
run;

Re: Dynamically call macro for different variables

Deyanamika — Tue, 12 Apr 2022 19:11:01 GMT

Hi,
I am trying to run the macro Na_update for 60 variables. So I stored the variable names in var_list. But I will try your solution if that works. Thanks

Re: Dynamically call macro for different variables

Deyanamika — Tue, 12 Apr 2022 19:11:52 GMT

Yeah that's actually a typo here... I typed on phone actually.. sorry for bad indentation.

Re: Dynamically call macro for different variables

Astounding — Tue, 12 Apr 2022 19:12:03 GMT

The problem is that each time you call the macro you begin with the original data set HAVE, and write over the new data set WANT. None of the changes from the previous iteration are saved.

When you have 60 variables that you want to process in the same way, the usual method is to create an array (not a macro). Then have a single DATA step process every variable in the array.

Re: Dynamically call macro for different variables

PaigeMiller — Tue, 12 Apr 2022 19:16:05 GMT

@Deyanamika wrote:
Yeah that's actually a typo here... I typed on phone actually.. sorry for bad indentation.

Please do not do this any more. It wastes your time, and it wastes our time. Please copy and paste the exact code you are using from now on.

Re: Dynamically call macro for different variables

Deyanamika — Wed, 13 Apr 2022 06:43:14 GMT

%macro na_update(var);

data want;

set have;

&var = tranwrd(&var,'NA','0');

if &var="." then do &var='0';

end;

run;

%mend;

data _NULL_; set have;

call execute('%nrstr(%na_update('||var_name||'))');

run;

Tried this, log shows no error. But still its not giving the desired output.

Re: Dynamically call macro for different variables

Kurt_Bremser — Wed, 13 Apr 2022 08:37:20 GMT

@Deyanamika wrote:

%macro na_update(var);

data want;

set have;

&var = tranwrd(&var,'NA','0');

if &var="." then do &var='0';

end;

run;

%mend;

data _NULL_; set have;

call execute('%nrstr(%na_update('||var_name||'))');

run;

Tried this, log shows no error. But still its not giving the desired output.

It can't. In every iteration you create want from have, so only the result of the last iteration will persist (somebody else already told you this). Your macro code itself is therefore wrong and useless.

So you want to translate a bunch of variables, the names of which are saved in a macro variable.

This is done in a single data step, using data step tools (namely, an ARRAY), no macro needed.

data want;
set have;
array all_my_vars {*} &var_list.;
do i = 1 to dim(all_my_vars);
  all_my_vars{i} = tranwrd(all_my_vars{i},"NA",'0');
  if all_my_vars{i} = "." then all_my_vars{i} = '0';
end;
drop i;
run;

Given that you iterate over a quite long list of variables, you might consider if it makes sense to transpose your dataset to a long layout. This is especially true if your variable names contain information which is in fact data (like dates or group identifiers).

Re: Dynamically call macro for different variables

Deyanamika — Wed, 13 Apr 2022 10:02:08 GMT

This worked. Thank you so much.