loop column names using macro

[radha009]

i have column names like  below, if data missing in that column insert null else insert App_id

proc contents data=patch.Windows_patch out=patch.contents noprint; run;

proc sql ;
   select name into :varlist separated by ' '
  from patch.contents where name like '%CHG%';
%let n=&obs;
quit;

data work;

set have;

do i = 1 to &n ;
    if &Patch {i} =' ' then
        &patch{i}=' '
    else
        &patch{i}=App_ID;
end;
run;

[Reeza]

Use a data step and an array instead. SQL doesn't lend itself well to variable lists and multiple variables well and then you can avoid using a macro entirely. 

---

@radha009 wrote:

i have column names like  below, if data missing in that column insert null else insert App_id

proc contents data=patch.Windows_patch out=patch.contents noprint; run;

proc sql ;
   select name into :varlist separated by ' '
  from patch.contents where name like '%CHG%';
%let n=&obs;
quit;

data work;

set have;

do i = 1 to &n ;
    if &Patch {i} =' ' then
        &patch{i}=' '
    else
        &patch{i}=App_ID;
end;
run;

---

[Reeza]

Sorry, must have misread your question. You have everything you need to do this and you don't need macros. 

If the variables were listed side by side you could also use:

array PATCH(*) FirstVarName -- LastVarName;

data work;

set have;
array Patch (*) &varList.;

do i = 1 to dim(Patch) ;
    if not missing(patch(i)) then patch(i) = APP_ID;
end;


run;

 

[Kurt_Bremser · Accepted solution]

And make the creation of varlist simpler:

proc sql noprint ;
select name into :varlist separated by ' '
from dictionary.columns
where
  libname = "PATCH" and
  memname = "WINDOWS_PATCH" and
  name like '%CHG%';
quit;

No proc contents needed.