topic How do I retain a value within a group based on several conditions? in New SAS User

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

efpolych84 — Wed, 25 Sep 2019 19:30:03 GMT

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

B 0 1500

B 1 1500

B 0 1500

C 0 1000

C 1 1000

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

PeterClemmensen — Wed, 25 Sep 2019 19:44:27 GMT

@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

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

efpolych84 — Wed, 25 Sep 2019 20:05:58 GMT

Thanks a lot!

Worked beautifully!

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

Ksharp — Thu, 26 Sep 2019 12:41:36 GMT

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;

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

efpolych84 — Thu, 26 Sep 2019 21:44:28 GMT

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!