BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PGStats
Opal | Level 21

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-d...

 

 

PG
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
PGStats
Opal | Level 21

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?

PG
ballardw
Super User

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

PGStats
Opal | Level 21

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

PG
PaigeMiller
Diamond | Level 26

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!

--
Paige Miller
Rick_SAS
SAS Super FREQ

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

PGStats
Opal | Level 21

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.

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 7 replies
  • 1679 views
  • 1 like
  • 4 in conversation