topic Re: Select by ID the most recent occurance in SAS Enterprise Guide

Select by ID the most recent occurance

Dogo23 — Tue, 17 Apr 2018 23:09:18 GMT

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;

Re: Select by ID the most recent occurance

ShenQicheng — Tue, 17 Apr 2018 23:43:46 GMT

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;

Re: Select by ID the most recent occurance

SuryaKiran — Wed, 18 Apr 2018 01:11:28 GMT

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.

Re: Select by ID the most recent occurance

Dogo23 — Wed, 18 Apr 2018 16:14:23 GMT

Super helpful. Thanks!