Desktop productivity for business analysts and programmers

Select by ID the most recent occurance

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Select by ID the most recent occurance

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;

 

 

Capture.PNG

  


Accepted Solutions
Solution
‎04-18-2018 12:14 PM
Contributor
Posts: 37

Re: Select by ID the most recent occurance

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎04-18-2018 12:14 PM
Contributor
Posts: 37

Re: Select by ID the most recent occurance

[ Edited ]

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;
PROC Star
Posts: 629

Re: Select by ID the most recent occurance

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. 

Thanks,
Suryakiran
Contributor
Posts: 64

Re: Select by ID the most recent occurance

Posted in reply to SuryaKiran

Super helpful. Thanks!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 166 views
  • 2 likes
  • 3 in conversation