BookmarkSubscribeRSS Feed
Deyanamika
Calcite | Level 5
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!
11 REPLIES 11
PeterClemmensen
Tourmaline | Level 20

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

Deyanamika
Calcite | Level 5
Yes, right. That's what I am looking for.
PeterClemmensen
Tourmaline | Level 20

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

%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. 

Kurt_Bremser
Super User

@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).

Deyanamika
Calcite | Level 5

This worked. Thank you so much.

PaigeMiller
Diamond | Level 26
%na_update(& next_val);

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

--
Paige Miller
Deyanamika
Calcite | Level 5
Yeah that's actually a typo here... I typed on phone actually.. sorry for bad indentation.
PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
Astounding
PROC Star

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 3232 views
  • 3 likes
  • 5 in conversation