topic Re: missing values in SAS Programming

missing values

vicky07 — Thu, 07 Dec 2017 03:19:01 GMT

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!

Re: missing values

Astounding — Thu, 07 Dec 2017 03:50:43 GMT

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;

Re: missing values

novinosrin — Thu, 07 Dec 2017 04:10:22 GMT

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;

Re: missing values

ChrisNZ — Thu, 07 Dec 2017 04:12:16 GMT

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;

Re: missing values

vicky07 — Thu, 07 Dec 2017 04:37:53 GMT

All 3 solutions worked as intended. Thank you so much!

Re: missing values

ballardw — Thu, 07 Dec 2017 23:57:59 GMT

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;