BookmarkSubscribeRSS Feed
brulard
Pyrite | Level 9

hi,

 

I need to join my table to the last date record of another table (an ID will have several records, where only the latest one is of interest).

 

I can do this this way, but it has the inconvenience of needing me to first query every single last record from the other table, prior to my performing the join. Pls let me know if there is a better way of doing this. Thanks,

 

Proc sql;create table want_preliminary as

Select *

From table_warehouse

Group by  id

Having bill_yr_mth=max(bill_yr_mth);quit;

 

Proc sql;create table  want_final as

Select b.amtount,b.bill_yr_mth,a.*

From have as a left join want_preliminary as b on

a.id=b.id ;quit;

5 REPLIES 5
Astounding
PROC Star

I'm sure there are some SQL wizards that could make this happen in a single SELECT statement.  But the main improvement you can make is on the first SELECT.  Instead of SELECT *, bring in only the fields you need:  SELECT id, bill_yr_mth, amtount

 

Once that is in place, your second SELECT statement can be simplified:

 

select a.*, b.* from have a, want_preliminary b where a.id=b.id;

 

There's no need for a left join when you are checking for matching IDs.

thomp7050
Pyrite | Level 9

Right.  To add on to what @Astounding said, you can do it in a single query, but it will not necessarily be more efficient.  Just less real estate on the code file (code not tested.)

 

 

Proc sql;
create table want_final as Select b.amtount,b.bill_yr_mth,a.* From have as a inner join ( Select id, amtount, bill_yr_mth From table_warehouse Group by id, amtount, bill_yr_mth Having bill_yr_mth=max(bill_yr_mth) b on b.id = a.id; quit;

 

brulard
Pyrite | Level 9
thanks Astounding.. ? though, there will be instances where there will be no record in table want_preliminary that match id from my original table. If I don't do a left join, will I not miss the records from my original table that got zero match during the join (I would need to keep all IDs/records from my original table)? thank you
brulard
Pyrite | Level 9

thanks Astounding.. ? though, there will be instances where there will be no record in table want_preliminary that match id from my original table. If I don't do a left join, will I not miss the records from my original table that got zero match during the join (I would need to keep all IDs/records from my original table)? thank you

PGStats
Opal | Level 21

Same, syntax fixed (I think)

Proc sql;
create table want_final as
Select 
	b.amtount,
	b.bill_yr_mth,
	a.*
From 
	have as a inner join 
	(	Select id, amtount, bill_yr_mth
		From table_warehouse
		Group by  id
		Having bill_yr_mth=max(bill_yr_mth) ) as b
	on b.id = a.id;
quit;
PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

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
  • 5 replies
  • 5725 views
  • 3 likes
  • 4 in conversation