topic Re: Matching main dataset to sub datasets based on date var in SAS Programming

Matching main dataset to sub datasets based on date var

Walternate — Wed, 21 Oct 2015 20:26:44 GMT

Hi,

I have a dataset at the person level with an ID and a date variable, and 5 sub-datasets, each of which represents the patients' data in a single year.

What I want is to join the dataset to the sub-datasets on ID, but I only want to pull in data from the sub-datasets in the sub-dataset representing the year closest to the year of the date variable, eg:

Main dataset:

ID Date

1 10/5/2010

2 1/3/2012

Sub dataset 2011:

ID var1 var2 var3

1 a b c

Sub dataset 2012:

ID var1 var2 var3

2 d e f

So after the join, it should be like this:

ID Date closest_var1 closest_var2 closest_var3

1 10/5/2010 a b c

2 1/3/2012 d e f

Any help is much appreciated.

Re: Matching main dataset to sub datasets based on date var

Patrick — Wed, 21 Oct 2015 21:07:50 GMT

It appears that the "date" for your sub-datasets is only available via data set name. You would need to derive the "date" from these names (what are the exact data set names?).

If you can't derive exact dates from your data set names: What does the year part stand for? End of year, start of year, ...?

That's relevant in order to match based on "closest" to an exact date in your main dataset.

Re: Matching main dataset to sub datasets based on date var

Walternate — Thu, 22 Oct 2015 13:02:23 GMT

Sub-dataset names:

addl_vars_2010

addl_vars_2011

addl_vars_2012

addl_vars_2013

addl_vars_2014

addl_vars_2015

The exact date should be counted as the end of the year (ie, Dec 31st).

Thanks!

Re: Matching main dataset to sub datasets based on date var

PGStats — Thu, 22 Oct 2015 15:59:26 GMT

Hi, I'm having trouble posting my answer. Another try:

Three ways to do this

proc sql;
create table want as
select m.date, s.* from main as m inner join sub2010 as s on m.id=s.id where year(m.date)=2010
union all
select m.date, s.* from main as m inner join sub2011 as s on m.id=s.id where year(m.date)=2011
union all
select m.date, s.* from main as m inner join sub2012 as s on m.id=s.id where year(m.date)=2012
union all
select m.date, s.* from main as m inner join sub2013 as s on m.id=s.id where year(m.date)=2013
union all
select m.date, s.* from main as m inner join sub2014 as s on m.id=s.id where year(m.date)=2014;
quit;

proc sql;
create table want as
select m.date, s.* from
	main as m inner join
	(	select 2010 as year, * from sub2010 union all
		select 2011 as year, * from sub2011 union all
		select 2012 as year, * from sub2012 union all
		select 2013 as year, * from sub2013 union all
		select 2014 as year, * from sub2014 ) as s
	on m.id=s.id and year(m.date) = s.year;
quit;

data s;
set sub: indsname=ds;
year = input(compress(ds,,"DK"), best.);
run;

proc sql;
create table want as
select m.date, s.* from main as m inner join s on m.id=s.id and year(m.date)=s.year;
drop table s;
quit;

(untested)

Re: Matching main dataset to sub datasets based on date var

Patrick — Thu, 22 Oct 2015 21:09:00 GMT

Below a first code drop. I'm not sure what "matching to closest data" actually means for you. May be you provide some more sample data to demonstrate how matching needs to work.

Hope below will give you already some ideas of how to solve your problem.

data main;
  input ID $ Date:ddmmyy10.;
  format date date9.;
  datalines;
1 10/5/2010
2 1/3/2012
;
run;

data addl_vars_2010;
  input (ID var1 var2 var3) ($);
  datalines;
1 a b c
;
run;

data addl_vars_2012;
  input (ID var1 var2 var3) ($);
  datalines;
2 d e f
;
run;

data All_Sub;
  set addl_vars_: indsname=inds;
  format date date9.;
  date=mdy(12,31,input(scan(inds,-1,'_'),16.));
run;

proc sql;
  create table want as 
  select L.*, R.var1, R.var2, R.var3
  from main L left join all_sub R
    on L.id=R.id and year(L.date)=year(R.date)
  ;
quit;