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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.