missing values

Accepted Solution Solved
Reply
Highlighted
Contributor
Posts: 73
Accepted Solution

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
a week ago
Super User
Posts: 5,615

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;

View solution in original post


All Replies
Solution
a week ago
Super User
Posts: 5,615

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: 1,800

Re: missing values

Posted in reply to Astounding

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;
Contributor
Posts: 73

Re: missing values

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

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: 11,578

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 143 views
  • 2 likes
  • 5 in conversation