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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.