topic Re: Find Max between 2 date for each id in SAS Programming

Find Max between 2 date for each id

hhchenfx — Sun, 17 Apr 2022 02:43:26 GMT

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;

Re: Find Max between 2 date for each id

Kurt_Bremser — Sun, 17 Apr 2022 08:12:08 GMT

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.

Re: Find Max between 2 date for each id

hhchenfx — Mon, 18 Apr 2022 03:36:49 GMT

This sql code work nicely.
I am still looking for way to fix my code.
Thanks,
HHC