Continous dates between start and end dates.

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Continous dates between start and end dates.

Hi,

I am trying to create an obs count based on overlapped and continuous dates.

Most of the logic is working as expected but when there are continuous dates. please see the example and code to if I make sense.

Result I have from below code:                                                                                  

ID            DOS                                          EOS                                             FLAG

1             06JUN2008:00:00:00                  17JUN2008:00:00:00                         1   

1             17JUN2008:00:00:00                   27JUN2008:00:00:00                        2

2              25FEB2009:00:00:00                 03MAR2009:00:00:00                       3

2             16AUG2009:00:00:00                  25AUG2009:00:00:00                       4

3              01SEP2009:00:00:00                 14SEP2009:00:00:00                       5

4              20APR2011:00:00:00                  22APR2011:00:00:00                       6

Code I have so far:

data want;

    set temp;

    by ID DOS EOS ;

     if first.ID and first.DOS and first.EOS then do;

      flag=_N_;

    end;

    retain flag ;

     if not first.ID and first.DOST and first.EOS then do ;

      if (DOS <= EOS) and (DOS >= EOS)  then flag+1;

     else flag = flag+1 ;

    end;

run;

I need:

ID            DOS                                          EOS                                             FLAG

1             06JUN2008:00:00:00                  17JUN2008:00:00:00                         1   

1             17JUN2008:00:00:00                   27JUN2008:00:00:00                        1

2              25FEB2009:00:00:00                 03MAR2009:00:00:00                       3

2             16AUG2009:00:00:00                  25AUG2009:00:00:00                       4

3              01SEP2009:00:00:00                 14SEP2009:00:00:00                       5

4              20APR2011:00:00:00                  22APR2011:00:00:00                       6

When the dates are continuous i.e. End of service (EOS) is 17JUN2008 for ID 1 and the date of service start date for same id is 17JUN2008. I need to count those ID's as 1.

Any suggestions on how to tackle this. Thanks in advance.

Thanks,

Jeeth.


Accepted Solutions
Solution
‎12-03-2014 08:27 PM
Esteemed Advisor
Posts: 7,284

Re: Continous dates between start and end dates.

Not sure if I correctly understand your logic, but the following will produce the desired values shown in your example:

data want;

  set temp;

  by ID;

  retain flag;

  flag=ifn(first.ID,_N_,ifn(DOS le lag(EOS)+1,flag,flag+1));

run;

View solution in original post


All Replies
Solution
‎12-03-2014 08:27 PM
Esteemed Advisor
Posts: 7,284

Re: Continous dates between start and end dates.

Not sure if I correctly understand your logic, but the following will produce the desired values shown in your example:

data want;

  set temp;

  by ID;

  retain flag;

  flag=ifn(first.ID,_N_,ifn(DOS le lag(EOS)+1,flag,flag+1));

run;

Contributor
Posts: 28

Re: Continous dates between start and end dates.

Hello Art,

Thanks for the quick response. Actually I also have to group the ids when the dates are overlapping and only increment the counter when the dates are not continuous and/or overlapping. My logic took care of the overlapping part but not when dates are continuous. I will incorporate your logic in my code and let you know how it goes.

Thanks,

Jeeth.

Contributor
Posts: 28

Re: Continous dates between start and end dates.

Hi Art,

Your logic is working perfectly. Thanks again for your suggestion. I have one small issue though. When dates are continuous like below. I still need to group them as one. I tried it couple of ways, but its still not working for me. I need ID 2 as counted as 2 and 2 when the dates are continuous.

ID            DOS                                          EOS                                             FLAG

1             06JUN2008:00:00:00                  17JUN2008:00:00:00                         1   

1             17JUN2008:00:00:00                   27JUN2008:00:00:00                        1

2              25FEB2009:00:00:00                 03MAR2009:00:00:00                        3

2             04MAR2009:00:00:00                  25AUG2009:00:00:00                       4

3              01SEP2009:00:00:00                 14SEP2009:00:00:00                        5

4             11SEP2009:00:00:00                  19SEP2009:00:00:00                        5

I changed my logic to :

data want;

    set temp;

    by ID ;

        retain flag ;

          flag=ifn(first.ID,_N_,ifn((DOS <> EOS)and DOS le lag(EOS)+1,flag,flag+1));

run;

Please let me know how to fix it. I tried changing DOS le lag(EOS)+1 to DOS ge lag(EOS)+1. Its completely changing counter value.

Thanks,

Jeeth.

Esteemed Advisor
Posts: 7,284

Re: Continous dates between start and end dates.

According to your latest example, ID is not relevant to the assignment of the value to flag. As such, you appear to only need:

data want;

  set temp;

  retain flag;

  flag=ifn(DOS le lag(EOS)+1,flag,_N_);

run;

Contributor
Posts: 28

Re: Continous dates between start and end dates.

Hi Art,

I do have to tie the IDs to the service effective and end dates. everything looks fine but my requirement is when the dates are overlapping or continuous, I need to tie the IDs based on that.

I need:

ID            DOS                                          EOS                                             FLAG

1             06JUN2008:00:00:00                  17JUN2008:00:00:00                         1   

1             17JUN2008:00:00:00                   27JUN2008:00:00:00                        1

2              25FEB2009:00:00:00                 03MAR2009:00:00:00                        2

2             04MAR2009:00:00:00                  25AUG2009:00:00:00                       2

3              01SEP2009:00:00:00                 14SEP2009:00:00:00                        3

3             11SEP2009:00:00:00                  19SEP2009:00:00:00                        3

3            25SEP2009:00:00:00                 29SEP2009:00:00:00                      4

In this flag is 1 fro ID 1 because, both the dates are continuous. Similarly, ID-2 dates are continuous as well. ID-3 first 2 lines, dates are overlapping therefore, same flag. As the last line is neither continuous nor overlapping, so it will have flag 4. Only ID-2 is not getting resolved regardless of what I try. If the flag numbers are continuous it would be great.

Thanks,

Jeeth.

Esteemed Advisor
Posts: 7,284

Re: Continous dates between start and end dates.

Your examples don't match:

ID            DOS                                          EOS                                             FLAG

1             06JUN2008:00:00:00                  17JUN2008:00:00:00                         1  

1             17JUN2008:00:00:00                   27JUN2008:00:00:00                        1

2              25FEB2009:00:00:00                 03MAR2009:00:00:00                        3

2             04MAR2009:00:00:00                  25AUG2009:00:00:00                       4

3              01SEP2009:00:00:00                 14SEP2009:00:00:00                        5

4             11SEP2009:00:00:00                  19SEP2009:00:00:00                        5

keeps the same flag across ids 3 and 4, while:

1             06JUN2008:00:00:00                  17JUN2008:00:00:00                         1  

1             17JUN2008:00:00:00                   27JUN2008:00:00:00                        1

2              25FEB2009:00:00:00                 03MAR2009:00:00:00                        2

2             04MAR2009:00:00:00                  25AUG2009:00:00:00                       2

3              01SEP2009:00:00:00                 14SEP2009:00:00:00                        3

3             11SEP2009:00:00:00                  19SEP2009:00:00:00                        3

3           25SEP2009:00:00:00                29SEP2009:00:00:00                     4

Has flags increment rather than reflect the value of _n_.

You'll have to state which rules you are trying to apply.

Contributor
Posts: 28

Re: Continous dates between start and end dates.

Hi Art,

Sorry if I wasn't clear.

This is what I am getting with the current logic I have:

ID            DOS                                          EOS                                             FLAG

1             06JUN2008:00:00:00                  17JUN2008:00:00:00                         1  

1             17JUN2008:00:00:00                   27JUN2008:00:00:00                        1

2              25FEB2009:00:00:00                 03MAR2009:00:00:00                        3

2             04MAR2009:00:00:00                  25AUG2009:00:00:00                       4

3              01SEP2009:00:00:00                 14SEP2009:00:00:00                        5

4             11SEP2009:00:00:00                  19SEP2009:00:00:00                        5

This is what I need.

1             06JUN2008:00:00:00                  17JUN2008:00:00:00                         1  

1             17JUN2008:00:00:00                   27JUN2008:00:00:00                        1

2              25FEB2009:00:00:00                 03MAR2009:00:00:00                        2

2             04MAR2009:00:00:00                  25AUG2009:00:00:00                       2

3              01SEP2009:00:00:00                 14SEP2009:00:00:00                        3

3             11SEP2009:00:00:00                  19SEP2009:00:00:00                        3

3           25SEP2009:00:00:00                29SEP2009:00:00:00                     4

There are multiple id's with different dates. I need to flag/group them based on ID and also the service start (DOS) and end dates (EOS). If the EOS of a previous record is same as the next record of DOS in bold, I need to group them together (Bolded dates). Your logic took care of this. I am not able to figure out the flag where the EOS has ended the previous day (03Mar2009) and next service for same ID has started very next day (i.e. on 04Mar2009). I am unable to figure this out. First two dates of ID 3 are overlapping. So, I need to flag them together. flag needs to be incremental based on these conditions. When a service is neither overlapping or continuous then count it as separate flag. Please let me know if I am still not clear.

Code I have so far:

data want;

    set temp;

    by ID ;

       if first.ID and first.DOSand first.EOS then do;

      flag=_N_;

    end;

     retain flag ;

          flag=ifn(first.ID,_N_,

            ifn((DOS <> EOS)and DOS le lag(EOS)+1 ,flag,flag+1));

run;

if I have this flag=ifn(DOS le lag(EOS)+1,flag,_N_); then the counter is skipping the numbers if they are repetitive as below.

1

1

3

3

5

6

Thanks,

Jeeth.

Esteemed Advisor
Posts: 7,284

Re: Continous dates between start and end dates.

There was an error in my original code, but I'm still not sure if I understand your requirements. The error in the original code was that it was treating one day as a value of 1, but you actually have a datetime variable.

Lets us know if the following does what you need:

data temp;

  informat DOS EOS datetime18.;

  format DOS EOS datetime18.;

  input id DOS EOS wantflag;

cards;

1  06JUN2008:00:00:00   17JUN2008:00:00:00   1 

1  17JUN2008:00:00:00   27JUN2008:00:00:00   1

2  25FEB2009:00:00:00   03MAR2009:00:00:00   2

2  04MAR2009:00:00:00   25AUG2009:00:00:00   2

3  01SEP2009:00:00:00   14SEP2009:00:00:00   3

3  11SEP2009:00:00:00   19SEP2009:00:00:00   3

3  25SEP2009:00:00:00   29SEP2009:00:00:00   4

;

data want;

  set temp;

  by ID;

  retain flag;

  if _n_ eq 1 then flag=0;

  flag=ifn(first.ID,flag+1,ifn(DOS le lag(EOS)+86400,flag,flag+1));

run;

Contributor
Posts: 28

Re: Continous dates between start and end dates.

Thanks again Art,

The code is working great. This is what I needed.

Thanks,

Jeeth.

Trusted Advisor
Posts: 1,202

Re: Continous dates between start and end dates.

data have;

input ID  (DOS  EOS)(:anydtdtm.);

format DOS  EOS datetime.;

datalines;

1    06JUN2008:00:00:00  17JUN2008:00:00:00

1    17JUN2008:00:00:00  27JUN2008:00:00:00

2    25FEB2009:00:00:00  03MAR2009:00:00:00

2    16AUG2009:00:00:00  25AUG2009:00:00:00

3    01SEP2009:00:00:00  14SEP2009:00:00:00

4    20APR2011:00:00:00  22APR2011:00:00:00

;

data want;

set have;

flag=_n_;

if dos=lag(eos) then flag=1;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 373 views
  • 6 likes
  • 3 in conversation