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

Hi,

I am trying to merge two data sets. One of the datasets looks like:

ID testdate1

1 09/30/2015

1 06/15/2016

1 01/05/2017

2 03/20/2017

2 04/22/2017

The number of records for each person varies.

 

The 2nd data set looks like:

ID testdate2

1 07/06/2016

1 01/02/2017

2 05/19/2017

2 10/20/2017

 

I can only match them using ID. What I want is:

ID  testdate1      testdate2    Indicator

1 06/15/2016   07/06/2016     1

1 01/05/2017  01/02/2017      1

 

If the interval between testdate1 and testdate2 is greater than 6 months, an indicator variable will be coded 1, otherwise the indicator variable is coded 0.

When I used the ID to merge the two datasets, it turns out to be

ID  testdate1      testdate2   Indicator

1  09/30/2015 07/06/2016  0

1 06/15/2016   01/02/2017 0

1 01/05/2017  01/02/2017  1

 

In the merged dataset above, the first person is mismatched. I want testdate1=06/15/2016 to be matched with 07/06/2016 and testdate1=09/30/2015 should not be matched with any records in data set 2.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@newbe wrote:

Thanks for your input! Problem is solved.


@newbe Please mark the appropriate solution or post the solution you found in the end. 

Thanks.

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20

Show us your code and log. Let's see if we can modify it. If nothing works out, we shall work on a new solution

newbe
Calcite | Level 5

My code is simple, but I don't think it is correct.

proc sort data=one;

by ID descending testdate1;

run;

proc sort data=two;

by ID descending testdate2;

run;

 

data three;

merge one two;

by ID;

run;

 

I created another variable in data set 1 and 2 by counting the number of observations for each person and then merge the two datasets using ID and the count. I am not sure whether this is correct.

data one1;

set one;

by ID descending testdate1;

if first.ID then count=0;

count+1;

run;

 

data two1;

set two;

by ID descending testdate2;

if first.ID then count=0;

count+1;

run;

 

data all;

merge one1 two1;

by ID count;

run;

 

novinosrin
Tourmaline | Level 20

seems like sql is better for this this:

 

data have1;
input iD testdate1 :mmddyy10.;
format testdate1 mmddyy10.;
datalines;
1 09/30/2015
1 06/15/2016
1 01/05/2017
2 03/20/2017
2 04/22/2017
;

data have2;
input iD testdate1 :mmddyy10.;
format testdate1 mmddyy10.;
datalines;
1 07/06/2016
1 01/02/2017
2 05/19/2017
2 10/20/2017
;

 

Try and let me know
proc sql;
create table want as
select *,intck('month',testdate1,testdate2)>6 as indicator
from have1 a, have2(rename=(testdate1=testdate2)) b
where a.id=b.id;

quit;

newbe
Calcite | Level 5

My description of the problem is not clear. Let me try again. What I really want is an indicator variable in the merged data set with a code of 1 indicating the time interval between testdate2 and testdate1 is less than 6 months and a code of 0 if the time interval between testdate2 and testdate1 is more than 6 months.

novinosrin
Tourmaline | Level 20

changing do lt (less than< 
proc sql;
create table want as
select *,intck('month',testdate1,testdate2)<6 as indicator
from have1 a, have2(rename=(testdate1=testdate2)) b
where a.id=b.id;

quit;

newbe
Calcite | Level 5

Thanks for your help! It doesn't work. Testdate1 and testdate2 are not in the same data set.

 

newbe
Calcite | Level 5

Thanks for your input! Problem is solved.

Reeza
Super User

@newbe wrote:

Thanks for your input! Problem is solved.


@newbe Please mark the appropriate solution or post the solution you found in the end. 

Thanks.

kiranv_
Rhodochrosite | Level 12

code copied from @novinosrin , add a case statement.

 

proc sql;
create table want as
select *, case when intck('month',a.testdate1,b.testdate2)<6 then 1 else 0 end as indicatior
from have1 a, have2 b
where a.id=b.id;

quit;

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 2409 views
  • 0 likes
  • 4 in conversation