Hi,
I have 2 datasets:
1st data has id, date and value
2nd data has id, start and end date.
I want to find the Max value between start and end date for each ID
I know my code below is wrong at the Point=i step but don't know how to fix.
Can you please help to fix or suggest solution?
Thanks,
HHC
data have;
input id date value;
datalines;
1 1 5
1 2 6
1 3 8
1 4 1
1 5 9999
1 6 99
1 7 11111
2 1 5
2 2 7
2 3 90
2 4 8
2 5 18
2 6 20
;run;
data condition;
input id start end;
datalines;
2 2 4
1 3 6
2 2 6
;run;
data w; set condition;
max=0;
set have (rename=(id=id2));
if id = id2 then do;
do i=start to end-1;
set have point=i;
if start<=date<end then do;
if max<value then max=value;
end;
end;
end;
run;
proc sql,
create table want as
select
t1.id,
t1.start,
t1.end,
max(t2.date) as maxdate
from condition t1 left join have t2
on t1.id = t2.id and t1.start le t2.date le t1.end
group by t1.id, t1.start, t1.end
;
quit;
Untested, posted from my tablet.
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!
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.
Ready to level-up your skills? Choose your own adventure.