BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vicky07
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Astounding
PROC Star

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;

ChrisNZ
Tourmaline | Level 20

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;
vicky07
Quartz | Level 8
All 3 solutions worked as intended. Thank you so much!
novinosrin
Tourmaline | Level 20

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;

ballardw
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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