How do I retain a value within a group based on several conditions?

[efpolych84]

Hello , 

I am relatively new to SAS and struggling with the following. Any help would be appreciated

Within each ID group if the max value of var1 = 0 then var2 should be the max(var2) for this group. (this part I can do with proc sql)

Within each group when the max value of var1=1 then var2 should be the max of var1 BEFORE the first obs where var1=1. This is the part that I can't figure out!

An example:

Data have;

ID   var1      var2   

 A       0        1000

 A       0        2000

 B       0        1500

 B       1        3000

 B       0        2000

 C       0       1000

 C       1        2000

Data want:

ID   var1      var2   

 A       0        2000

 A       0        2000

 B       0        1500

 B       1        1500

 B       0        1500

 C       0       1000

 C       1        1000

[PeterClemmensen · Accepted solution]

@efpolych84 Hi and welcome to the SAS Community!

You can do something like this

data have;
input ID $ var1 var2;
datalines;
A 0 1000
A 0 2000
B 0 1500
B 1 3000
B 0 2000
C 0 1000
C 1 2000
;

data want(drop=_:);
   _iorc_=.;
   do until (last.id);
      set have;
      by id;
      if var1=1 then _var2=_iorc_;
      if var2 > _iorc_ then _iorc_=var2;
   end;
   do until (last.id);
      set have;
      by id;
      var2=coalesce(_var2, _iorc_);
      output;
   end;
run;

Result:

ID var1 var2 
A  0    2000 
A  0    2000 
B  0    1500 
B  1    1500 
B  0    1500 
C  0    1000 
C  1    1000 

[efpolych84]

Thanks a lot! 

Worked beautifully!

[Ksharp]

data have;
input ID $ var1 var2;
datalines;
A 0 1000
A 0 2000
B 0 1500
B 1 3000
B 0 2000
C 0 1000
C 1 2000
;
data temp;
 set have;
 by id;
 retain found ;
 if first.id then found=.;
 if var1=1 then found=1;
 if not found;
run;
proc sql;
create table want as
 select a.*,b.v as max
  from have as a left join (select id,max(var2) as v from temp group by id) as b
   on a.id=b.id;
quit;

[efpolych84]

This one works great as well! Thanks so much! 

It is so good to see multiple answers, since I had been trying to figure this is out on my own before posting and it's good to find out what I was missing with each one of my approaches!