topic Re: Macro loop through selected columns in SAS Programming

Macro loop through selected columns

someone_new — Mon, 24 Aug 2020 12:20:14 GMT

Hi,

Wanna ask any efficient ways to do same operations on multiple columns?

Example:
I have a macro named "%fun" and column names of
A1 A2 A3 A4 A5 A6 B1 B2 B3 B4 B5

So i wish to loop the macro %fun across columns A4 to B4, any ideas how to code this? (ps. In real dataset, I have about 300 columns)

Re: Macro loop through selected columns

Kurt_Bremser — Mon, 24 Aug 2020 12:28:40 GMT

What does the macro do? It might be that you don't have to call the macro repeatedly, but can use a data step do loop over an array, or similar.

Re: Macro loop through selected columns

someone_new — Mon, 24 Aug 2020 12:32:30 GMT

Basically the macro %fun retain a previous value and place into missing value or cells

Re: Macro loop through selected columns

Kurt_Bremser — Mon, 24 Aug 2020 12:54:31 GMT

That can be done in a single data step, using an array for the variables and a temporary array of same size for the retained values.

All you need to create first is a macro variable with the variable names, for the array definitions; the size of the temporary array can be determined from that with %sysfunc(countw()).

Show the code you run for a single variable, and we'll show you how to expand it.

Re: Macro loop through selected columns

someone_new — Mon, 24 Aug 2020 13:05:41 GMT

%macro fun(variable) ;
retain _&variable;
if &variable ^=. then _&variable = &variable;
else &variable = _&variable;
drop _&variable;
%mend;

Re: Macro loop through selected columns

Kurt_Bremser — Mon, 24 Aug 2020 13:45:52 GMT

%let vars = a b c;

data want;
set have;
array have {*} &vars.;
array ret {%sysfunc(countw(&vars.))} _temporary_;
do i = 1 to dim(have);
  if have{i} ne .
  then ret{i} = have{i};
  else have{i} = ret{i};
end;
drop i;
run;

Untested, posted from my tablet. If it works, all you have to do is to populate the macro variable.

Re: Macro loop through selected columns

someone_new — Mon, 24 Aug 2020 14:03:15 GMT

How about range of columns instead of %let vars method?

Re: Macro loop through selected columns

someone_new — Mon, 24 Aug 2020 14:57:15 GMT

Yours solution works perfectly thank you so muchhhhhh you save my day thank you!!!

Re: Macro loop through selected columns

Tom — Mon, 24 Aug 2020 15:03:11 GMT

@someone_new wrote:
How about range of columns instead of %let vars method?

If you want to use a variable list (var1-var10 for example) instead of naming each variable then you need to modify the data step to just make the temporary array large enough for any possible real use.

%let vars = a b var1-var20 firstvar-numeric-lastvar;
...
array have {*} &vars.;
array ret {1000} _temporary_;

Re: Macro loop through selected columns

Kurt_Bremser — Mon, 24 Aug 2020 16:54:41 GMT

You need to know the number of variables before you enter the data step, to correctly define the temporary array. So you will need to determine the list of variables, anyway.

@someone_new wrote:
How about range of columns instead of %let vars method?

Re: Macro loop through selected columns

Tom — Mon, 24 Aug 2020 17:03:20 GMT

You just need to know an upper bound on the number the number of variables. The size of the temporary array does not matter as long as it is at least as large as the actual array.

Re: Macro loop through selected columns

Kurt_Bremser — Mon, 24 Aug 2020 17:05:18 GMT

My inner old-school coder simply does not want to waste memory 😉

Re: Macro loop through selected columns

someone_new — Fri, 04 Sep 2020 13:21:27 GMT

Hi @Kurt_Bremser i have new challenging problem mind help me solve it? I have posted in my profile