topic Re: Join as of 4 different dates in SAS Programming

Join as of 4 different dates

Walternate — Mon, 21 Mar 2016 14:37:05 GMT

Hi,

I have a dataset at the person level that has ID and yearly values of a secondary ID that link to Dataset 2:

Dataset 1:

ID Dataset2_ID_2009 Dataset2_ID_2010 Dataset2_ID_2011

1 123 123 234

2 234 456

As you can see, the secondary ID can be missing in a given year, and can either change or stay the same from year to year.

Dataset 2 has the Dataset2 IDs in rows with a start and end date, as well as two categorical vars that are true as of the start and end dates:

Dataset 2:

Dataset_2_ID Start_dt End_dt Categ_var1 Categ_var2

123 1/3/2000 10/02/2012 abc def

123 2/5/2013 12/01/2013 ghi jkl

234 7/1/2001 9/3/2012 abc def

456 9/3/2001 4/5/2003 ghi jkl

456 8/7/2010 5/17/2014 mno pqr

What I want is to keep the form of Dataset 1 but pull in the value of Categ_var1 and Categ_var2 as of January 1st of the year of Dataset_2_ID (for all years), like this:

Final dataset:

ID Dataset2_ID_2009 Categ_var_1_2009 Categ_var_2_2009 Dataset2_ID_2010 Categ_var_1_2010 Categ_var_2_2010

1 123 abc def 123 abc def

(first row continued)

Dataset2_ID_2011 Categ_var_1_2011 Categ_var_2_2011

234 abc def

I know how to do this the long way by splitting Dataset 1 into yearly cohorts and doing a join for each year, but I'm hoping that there is a way to do it with fewer steps.

Any help is much appreciated.

Re: Join as of 4 different dates

LinusH — Mon, 21 Mar 2016 15:06:23 GMT

Dataset1 has bit odd layout. And I'm a bit disturb about dataset 2 id value 234 repeats between different dataset 1 id's..?
Assuming the your yearly cohorts are defined by the dataset 2 id's in dataset1.
And, having data in wide format almost always complicates stuff. Transpose dataset1 and the it's a matter of a simple join operation.

Re: Join as of 4 different dates

RW9 — Mon, 21 Mar 2016 15:19:33 GMT

I agree with @LinusH, your first dataset looks odd. I haven't time as leaving now, but re-arrange your code so that "data" items are in columns, this will make your life much easier:

ID YEAR LINK

1 2009 123

1 2010 123

...

Its never a good idea to put "data" in column names.

Re: Join as of 4 different dates

mbuchecker — Mon, 21 Mar 2016 16:53:56 GMT

You could transpose the first data set, join, and then re-transpose. The following code is not complete but should get you going in the right direction.

proc transpose data=work.one out=transone;
id id;
var Dataset2_ID_2009 Dataset2_ID_2010 Dataset2_ID_2011;
run;
data transone2 (drop=_name_);
set transone;
_name_ = substr(right(_name_), 13, 4);
year=input(_name_, 4.);
run;

proc sql;
select *
from transone2, two
where year between year(start_dt) and year(end_dt);
run;