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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.