Macro loop through selected columns

[someone_new]

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)

[Kurt_Bremser]

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.

[someone_new]

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

[Kurt_Bremser]

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.

[someone_new]

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

[Kurt_Bremser · Accepted solution]

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

[someone_new]

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

[someone_new]

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

[Tom]

---

@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_;

[Kurt_Bremser]

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?

---

[Tom]

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.

[Kurt_Bremser]

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

[someone_new]

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