missing values

Solved
Highlighted
Frequent Contributor
Posts: 76

missing values

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!

Accepted Solutions
Solution
‎12-06-2017 11:35 PM
Super User
Posts: 6,409

Re: missing values

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;

All Replies
Solution
‎12-06-2017 11:35 PM
Super User
Posts: 6,409

Re: missing values

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;

PROC Star
Posts: 2,136

Re: missing values

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;```
Frequent Contributor
Posts: 76

Re: missing values

All 3 solutions worked as intended. Thank you so much!
PROC Star
Posts: 1,100

Re: missing values

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;

Super User
Posts: 12,742

Re: missing values

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;
```
☑ This topic is solved.