topic Re: PROC MERGE? or another function? in SAS Programming

PROC MERGE? or another function?

Kristinos — Wed, 08 Oct 2014 17:44:22 GMT

Hello!

I am trying to merge two datasets, but I am not sure if what I want it is possible?

I want the DATE (in data2) to fit within the correct period (in data1), which has a start_date and an end_date…

Data1:

ID Period Start_date End_date

10 1 01.01.2012 31.12.2013

10 2 01.01.2013 31.12.2014

10 3 01.01.2014 31.12.2015

20 1 01.06.2012 31.05.2013

20 2 01.06.2013 31.05.2014

Data2:

ID DATE YIELD

10 01.03.2013 20

10 01.05.2013 30

10 01.10.2014 50

10 01.11.2015 60

20 01.09.2013 70

This is how I want the output to look like:

ID Number Start_date End_date DATE YIELD

10 1 01.01.2012 31.12.2013 01.03.2013 20

10 1 01.01.2012 31.12.2013 01.05.2013 30

10 2 01.01.2013 31.12.2014 01.10.2014 50

10 3 01.01.2014 31.12.2015 01.11.2015 60

20 1 01.06.2012 31.05.2013 . .

20 2 01.06.2013 31.05.2014 01.09.2013 70

So far my SAS-skills haven't succeed, hopefully there are someone out there who could help?

Thank you!

Re: PROC MERGE? or another function?

Reeza — Wed, 08 Oct 2014 19:03:15 GMT

You can use SQL join where the join is using a between. One of the few cases where SQL is better than a data step.

untested:

proc sql;

create table want as

select a.*, b.*

from data1 as a

full join data2 as b

on b.date between a.start_date and b.end_date;

quit;

Re: PROC MERGE? or another function?

Kristinos — Wed, 08 Oct 2014 19:17:54 GMT

Amazing how easy it can be done... Should have learned SQL, but there is no time...

THANK YOU VERY MUCH!!

Re: PROC MERGE? or another function?

art297 — Wed, 08 Oct 2014 19:24:01 GMT

I disagree that you want a full join. However, I also think you are missing some matches that exist in your data. I suggest trying the following:

proc sql noprint;

create table want as

select *

from data1 a

left join data2 b

on a.id=b.id

where a.start_date<=b.date<=a.end_date

;

quit;

Re: PROC MERGE? or another function?

Reeza — Wed, 08 Oct 2014 19:35:06 GMT

There's an obs in Data2 that is in the final file, so I assumed full join...I'm assuming the OP's post is correct of course

Re: PROC MERGE? or another function?

Kristinos — Wed, 08 Oct 2014 19:53:07 GMT

You are right Arthur Tabachneck.

With the first version all data in data2 were matched with both ID,

but your SQL seems to work.

THANK YOU!!