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.
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.
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!
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;
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.