BookmarkSubscribeRSS Feed
hhchenfx
Rhodochrosite | Level 12

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;

 

2 REPLIES 2
Kurt_Bremser
Super User
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.

hhchenfx
Rhodochrosite | Level 12
This sql code work nicely.
I am still looking for way to fix my code.
Thanks,
HHC

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 617 views
  • 2 likes
  • 2 in conversation