BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dogo23
Quartz | Level 8

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

  

1 ACCEPTED SOLUTION

Accepted Solutions
ShenQicheng
Obsidian | Level 7

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

3 REPLIES 3
ShenQicheng
Obsidian | Level 7

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
Meteorite | Level 14

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
Dogo23
Quartz | Level 8

Super helpful. Thanks!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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