topic Impossible to post an answer in All Things Community

Impossible to post an answer

PGStats — Thu, 22 Oct 2015 16:06:38 GMT

Hello, I am encountering a situation where it is impossible for me to post an answer to a topic. I tried three times. Every time I submit a post, it is like nothing happened. The topic is

https://communities.sas.com/t5/Base-SAS-Programming/Matching-main-dataset-to-sub-datasets-based-on-date-var/m-p/231195#M42034

Re: Impossible to post an answer

PaigeMiller — Thu, 22 Oct 2015 16:17:26 GMT

Looks like the original message has been deleted, so that would mean you can't reply to it.

Re: Impossible to post an answer

PGStats — Thu, 22 Oct 2015 16:23:12 GMT

I can still see the message. The Reply button on top is dimmed, but the one on the bottom is active. What makes you say the message has been deleted?

Re: Impossible to post an answer

ballardw — Thu, 22 Oct 2015 17:28:20 GMT

It may be a browser cache issue that you see it. I tried the link and got a 'not found' page.

Re: Impossible to post an answer

PGStats — Thu, 22 Oct 2015 17:31:25 GMT

Thanks @ballardw and @PaigeMiller. Too bad, I had a good answer to the question .

Re: Impossible to post an answer

PaigeMiller — Thu, 22 Oct 2015 17:33:53 GMT

I get a "message not found" error when I click on the link.

However, there's no reason you have to waste a good answer! You can post the answer here!

Re: Impossible to post an answer

Rick_SAS — Thu, 22 Oct 2015 17:35:35 GMT

The Communities was down for a while in that time period. I got a message that said "Doing maintenance." Maybe the OP will repost!

Re: Impossible to post an answer

PGStats — Thu, 22 Oct 2015 17:38:21 GMT

Good idea @PaigeMiller, The question was

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.

and my answer was:

Three methods:

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;

There, I've said it. Feels better.