topic Re: Left join to last record in SAS Programming

Left join to last record

brulard — Thu, 27 Apr 2017 18:55:38 GMT

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;

Re: Left join to last record

Astounding — Thu, 27 Apr 2017 19:08:05 GMT

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.

Re: Left join to last record

thomp7050 — Thu, 27 Apr 2017 19:15:08 GMT

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;

Re: Left join to last record

PGStats — Thu, 27 Apr 2017 22:26:56 GMT

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;

Re: Left join to last record

brulard — Fri, 28 Apr 2017 10:35:40 GMT

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

Re: Left join to last record

brulard — Mon, 01 May 2017 11:05:57 GMT