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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 634 views
  • 2 likes
  • 2 in conversation