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
@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 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
Thanks a lot!
Worked beautifully!
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;
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!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.