Hi,
I have a question and hoping someone would help me solve this problem. I have the below data and I am trying to fill in the blanks with 'N' for column M1 - M7.
ID | M1 | M2 | M3 | M4 | M5 | M6 | M7 |
1873 | Y | ||||||
3333 | Y | ||||||
78928 | Y | Y | |||||
26544 | Y | Y | |||||
4532 | Y | Y | Y | Y |
The challenge is I want to put 'N' only in front of the column(s) where there is a 'Y. Below is the desired output.
ID | M1 | M2 | M3 | M4 | M5 | M6 | M7 |
1873 | Y | ||||||
3333 | N | N | Y | ||||
78928 | N | Y | N | N | Y | ||
26544 | Y | N | Y | ||||
4532 | Y | N | Y | N | Y | N | Y |
In the first row there is no 'N' because I want 'N's only in front of columns where there is a 'Y'.
Thanks in advance for the help!
One approach:
data want;
set have;
array m {7};
do k=7 to 1 by -1 until (m{k}='Y');
end;
if k > 1 then do k = k-1 to 1 by -1;
if m{k} = ' ' then m{k} = 'N';
end;
drop k;
run;
One approach:
data want;
set have;
array m {7};
do k=7 to 1 by -1 until (m{k}='Y');
end;
if k > 1 then do k = k-1 to 1 by -1;
if m{k} = ' ' then m{k} = 'N';
end;
drop k;
run;
Another, similar, approach:
data want; set have; array m {7}; do k=7 to 1 by -1 ; if m{k}='Y' then yfound=1; if yfound & m{k}=' ' then m{k}='N'; end; drop yfound k; run;
data want;
set have;
array t(*)$ m1-m7;
do _i=dim(t) to 1 by -1;
if t(_i)='y' then do;
_flag=1;
continue;
end;
if t(_i)=' ' and _flag then t(_i)='N';
end;
drop _:;
run;
And just for giggles a slight variation that has some obfuscation:
data want; set have; array m m1-m7; do _i_=1 to ( findc(cat(of m(*)),'Y',-7) ); if missing(m[_i_]) then m[_i_] = 'N'; end; run;
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.