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;
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.
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;
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;
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.