Help using Base SAS procedures

Conditional joins

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

Conditional joins

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

So data set 1:

PlayerDateValue
14/5/20125
15/23/201212
15/30/20127

dataset two:

PlayerTeamstart date
1ABC4/3/2012
1BCD5/12/2012
1CDE5/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.


Accepted Solutions
Solution
‎01-28-2013 04:01 AM
Super User
Posts: 5,257

Re: Conditional joins

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;

Data never sleeps

View solution in original post


All Replies
Respected Advisor
Posts: 4,651

Re: Conditional joins

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;

PG

PG
Valued Guide
Posts: 2,175

Re: Conditional joins

IT also looks like an opportunity for an interleaved SET statement

Solution
‎01-28-2013 04:01 AM
Super User
Posts: 5,257

Re: Conditional joins

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;

Data never sleeps
Frequent Contributor
Posts: 82

Re: Conditional joins

I think this is working. Thanks!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 340 views
  • 3 likes
  • 4 in conversation