DATA Step, Macro, Functions and more

How to identify cases that need to be combined through dates and location numbers?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How to identify cases that need to be combined through dates and location numbers?

Hi, I have a data like the table below (also attached excel data).  I need to create groupID by certain rules:

1. if locationID2 for previous row=locationID1 for the current row and if (intime for current row - outtime for previous <=1 ), then the rows are still put into the same group, e.g., groupID=1

2. Even if locationID2 for previous row not equal to locationID1 for the current row, as long as (intime for current row - outtime for previous <=1 ), then the rows are still put into the same group, e.g., groupID=2

3. Sometimes, there may be more than 2 cases in one group, e.g., groupID=3

4. Within the same person, groupID need to be in an increasing order

5. All other rows, groupID are assigned to be 0

Thanks for your help.

personintimelocationIDouttimelocationID2groupID
12/17/200912/25/200900
13/14/200913/26/200900
15/21/200915/25/200900
16/6/200916/10/200900
16/19/200916/22/200941
16/22/200947/3/200901
17/11/200917/12/200962
17/12/200977/17/200902
18/5/200918/10/200900
15/6/201115/12/201100
18/10/201118/13/201133
18/13/201138/15/201173
18/15/201178/24/201103
Attachment

Accepted Solutions
Solution
‎07-18-2013 01:23 PM
Respected Advisor
Posts: 3,156

Re: How to identify cases that need to be combined through dates and location numbers?

Very interesting question, quite a few mind-twisted turns, the following code seems to work for your presented data:

data have;

input person      intime :mmddyy10. locationID outtime :mmddyy10.      locationID2 ;

format intime outtime mmddyy10.;

cards;

1     2/17/2009   1     2/25/2009   0    

1     3/14/2009   1     3/26/2009   0    

1     5/21/2009   1     5/25/2009   0    

1     6/6/2009    1     6/10/2009   0    

1     6/19/2009   1     6/22/2009   4    

1     6/22/2009   4     7/3/2009    0    

1     7/11/2009   1     7/12/2009   6    

1     7/12/2009   7     7/17/2009   0    

1     8/5/2009    1     8/10/2009   0    

1     5/6/2011    1     5/12/2011   0    

1     8/10/2011   1     8/13/2011   3    

1     8/13/2011   3     8/15/2011   7    

1     8/15/2011   7     8/24/2011   0    

;

data want;

   set have;by person;

   set have(firstobs=2 rename=(intime=_intime locationid=_locationid)

            keep=locationid intime)

       have(obs=1 drop=_all_);

         retain group 0;

   if first.person then _group=0;

   if last.person then call missing(of _Smiley Happy;

      _a=(_intime-outtime <= 1 and _locationid - locationid2 <=1);

      _b=(intime-lag(outtime) <=1 and locationid-lag(locationid2) <=1);

   if _a and not _b then do;_group+1; group=_group;end;

   if not _a and not _b then group=0;

      drop _:;

run;

Haikuo

View solution in original post


All Replies
Solution
‎07-18-2013 01:23 PM
Respected Advisor
Posts: 3,156

Re: How to identify cases that need to be combined through dates and location numbers?

Very interesting question, quite a few mind-twisted turns, the following code seems to work for your presented data:

data have;

input person      intime :mmddyy10. locationID outtime :mmddyy10.      locationID2 ;

format intime outtime mmddyy10.;

cards;

1     2/17/2009   1     2/25/2009   0    

1     3/14/2009   1     3/26/2009   0    

1     5/21/2009   1     5/25/2009   0    

1     6/6/2009    1     6/10/2009   0    

1     6/19/2009   1     6/22/2009   4    

1     6/22/2009   4     7/3/2009    0    

1     7/11/2009   1     7/12/2009   6    

1     7/12/2009   7     7/17/2009   0    

1     8/5/2009    1     8/10/2009   0    

1     5/6/2011    1     5/12/2011   0    

1     8/10/2011   1     8/13/2011   3    

1     8/13/2011   3     8/15/2011   7    

1     8/15/2011   7     8/24/2011   0    

;

data want;

   set have;by person;

   set have(firstobs=2 rename=(intime=_intime locationid=_locationid)

            keep=locationid intime)

       have(obs=1 drop=_all_);

         retain group 0;

   if first.person then _group=0;

   if last.person then call missing(of _Smiley Happy;

      _a=(_intime-outtime <= 1 and _locationid - locationid2 <=1);

      _b=(intime-lag(outtime) <=1 and locationid-lag(locationid2) <=1);

   if _a and not _b then do;_group+1; group=_group;end;

   if not _a and not _b then group=0;

      drop _:;

run;

Haikuo

New Contributor
Posts: 2

Re: How to identify cases that need to be combined through dates and location numbers?

Hi Hai Kuo,

Thank you so much, it works perfectly! 

I really appreciate your help.

Have a nice day.

Huijuan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 197 views
  • 1 like
  • 2 in conversation