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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

10 REPLIES 10
art297
Opal | Level 21

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;

jeeth79usa
Calcite | Level 5

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.

jeeth79usa
Calcite | Level 5

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.

art297
Opal | Level 21

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;

jeeth79usa
Calcite | Level 5

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.

art297
Opal | Level 21

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.

jeeth79usa
Calcite | Level 5

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.

art297
Opal | Level 21

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;

jeeth79usa
Calcite | Level 5

Thanks again Art,

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

Thanks,

Jeeth.

stat_sas
Ammonite | Level 13

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;

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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