topic Re: Continous dates between start and end dates. in SAS Programming

Continous dates between start and end dates.

jeeth79usa — Wed, 03 Dec 2014 23:17:01 GMT

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.

Re: Continous dates between start and end dates.

art297 — Thu, 04 Dec 2014 01:27:45 GMT

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;

Re: Continous dates between start and end dates.

stat_sas — Thu, 04 Dec 2014 01:32:23 GMT

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;

Re: Continous dates between start and end dates.

jeeth79usa — Thu, 04 Dec 2014 02:45:13 GMT

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.

Re: Continous dates between start and end dates.

jeeth79usa — Thu, 04 Dec 2014 18:23:17 GMT

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.

Re: Continous dates between start and end dates.

art297 — Thu, 04 Dec 2014 18:43:41 GMT

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;

Re: Continous dates between start and end dates.

jeeth79usa — Thu, 04 Dec 2014 22:08:13 GMT

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

25SEP2009:00:00:00

29SEP2009:00:00:00

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.

Re: Continous dates between start and end dates.

art297 — Thu, 04 Dec 2014 22:24:45 GMT

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

25SEP2009:00:00:00

29SEP2009:00:00:00

Has flags increment rather than reflect the value of _n_.

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

Re: Continous dates between start and end dates.

jeeth79usa — Fri, 05 Dec 2014 16:52:05 GMT

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

25SEP2009:00:00:00

29SEP2009:00:00:00

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.

Thanks,

Jeeth.

Re: Continous dates between start and end dates.

art297 — Fri, 05 Dec 2014 18:41:22 GMT

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;

Re: Continous dates between start and end dates.

jeeth79usa — Fri, 05 Dec 2014 20:29:56 GMT

Thanks again Art,

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

Thanks,

Jeeth.