topic Re: Conditional joins in SAS Procedures

Conditional joins

Xamius32 — Sat, 26 Jan 2013 22:51:58 GMT

Lets say I want to join two tables, one of which has different values to join based on the date.

So data set 1:

Player	Date	Value
1	4/5/2012	5
1	5/23/2012	12
1	5/30/2012	7

dataset two:

Player	Team	start date
1	ABC	4/3/2012
1	BCD	5/12/2012
1	CDE	5/23/2012

So I want another column in dataset 1 to give me the team for the specific date. So the new column would have ABC for the first one, and CDE for the second and third observation.

Does that make sense?

Any help is appreciated.

Re: Conditional joins

PGStats — Sat, 26 Jan 2013 23:38:58 GMT

Try this :

proc sql;

create table three as

select one.player, one.date, one.value, two.team, two.startDate

from one inner join two on one.player=two.player and startDate<=date

group by one.player, one.date

having two.startDate=max(two.startDate);

quit;

Re: Conditional joins

Peter_C — Sun, 27 Jan 2013 13:53:48 GMT

IT also looks like an opportunity for an interleaved SET statement

Re: Conditional joins

LinusH — Mon, 28 Jan 2013 09:01:23 GMT

If this is a common query, you may consider to store table two using SCD type 2 style, by creating an end data for each team membership row. The current row will have a "hig-date", a date that will never exist (at least during our life time.

You can easily create this on existing data by using retain for the previous records start data (in a data step).

In this way, queries like this gets much simpler, just using a between-and join condition:

proc sql;

create table three as

select one.player, one.date, one.value, two.team, two.startDate

from one inner join two

on one.player = two.player and

one.Date between two.startDate and two.EndDate

;

quit;

Re: Conditional joins

Xamius32 — Tue, 29 Jan 2013 04:35:48 GMT

I think this is working. Thanks!