Select by ID the most recent occurance

[Dogo23]

Hi all,

I have a table with transactional data that are labeled for a confirmation_num. These confirmation numbers duplicated each time an update is made to the order by incrementally adding a number to the confirmation_page field. I need to select the most recent occurrence for each confirmation_num. 

The code below brought me back nothing. Thoughts?

proc sql;
create table t1 as 
SELECT confirmation_num,
input(confirmation_page, best12.) as confirmation_page,
ARRIVAL_DAY_KEY,
DEPARTURE_DAY_KEY
from MC_RESERVATION
where DEPARTURE_DAY_KEY between 20180418 and 20180424
having confirmation_num = max(confirmation_page);
quit;

[ShenQicheng · Accepted solution]

Maybe this works?

proc sql;
	create table t1 as 
		SELECT confirmation_num,
			input(confirmation_page, best12.) as confirmation_page,
			ARRIVAL_DAY_KEY,
			DEPARTURE_DAY_KEY
		from MC_RESERVATION
			where DEPARTURE_DAY_KEY between 20180418 and 20180424
				group by confirmation_num
					having confirmation_page = max(confirmation_page)
	;
quit;

[SuryaKiran]

Did you try sorting the data  and then using LAST. to get the most recent record.

proc sort data=MC_RESERVATION;
by Confirmation_num Confirmation_Page;

DATA WANT ;
SET MC_RESERVATION;
by Confirmation_num Confirmation_Page;
if last.Confirmation_Page and 20180418<=Departure_day_key<=20180424;
run;

If your Confirmation_Page is character then convert it to numeric because if you have values 1,2,11 then sort will be 1,11,2 if the variable is character. 

Also why do you have the date values as numeric, best practice is treating them with date formats. 

[Dogo23]

Super helpful. Thanks!