DATA Step, Macro, Functions and more

Left join to last record

Reply
Frequent Contributor
Posts: 95

Left join to last record

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;

Super User
Posts: 5,516

Re: Left join to last record

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.

Frequent Contributor
Posts: 93

Re: Left join to last record

Posted in reply to Astounding

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;

 

Frequent Contributor
Posts: 95

Re: Left join to last record

Posted in reply to Astounding
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
Frequent Contributor
Posts: 95

Re: Left join to last record

Posted in reply to Astounding

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

Respected Advisor
Posts: 4,930

Re: Left join to last record

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
Ask a Question
Discussion stats
  • 5 replies
  • 194 views
  • 3 likes
  • 4 in conversation