topic Re: How to find ineligible date range from a date range in SAS Programming

How to find ineligible date range from a date range

erbmani — Wed, 08 Feb 2017 22:05:18 GMT

Hi,

This is my first post!

I have two set of date ranges. One is eligibility and other lockin dates. I want to populate dates which are not covered under Lockin date range (compared against Eligibility). Below is just one example as i need to handle variables to achieve this.

Participant Elig.Start Elig.Stop Lockin.Start Lockin.Stop

ABC1 09/02/2010 10/14/2010 04/16/2010 03/29/2012

Expected output:

Participant Elig.Start Elig.Stop Lockin.Start Lockin.Stop Ineligible.start Ineligible.stop

ABC1 09/02/2010 10/14/2010 04/16/2010 03/29/2012 04/16/2010 09/01/2010

ABC1 09/02/2010 10/14/2010 04/16/2010 03/29/2012 10/15/2010 03/29/2012

Re: How to find ineligible date range from a date range

Reeza — Wed, 08 Feb 2017 23:40:52 GMT

What are the rules, how do you know when it starts/stops?

Re: How to find ineligible date range from a date range

ballardw — Wed, 08 Feb 2017 23:49:50 GMT

Also before we go very far are your dates SAS date values or character (is their format mmddyy10 or $10 or similar)? If we need to compare dates the first thing will be to generate SAS date values.

Re: How to find ineligible date range from a date range

mkeintz — Thu, 09 Feb 2017 02:07:34 GMT

You apparently want to output 2 records for every incoming record. There are 2 new variables: ineligstart and ineligstop.

The first record has ineligstart=lockinstart and ineligstop=1 day prior to eligstart.

The 2nd record has ineligstart=1 day after eligstop and ineligstop=lockinstop.

Assuming, you have date numeric values not date character values (as per @ballardw note), then this would work:

data have;
input Participant :$4. 
  (EligStart EligStop LockinStart  LockinStop) (:mmddyy10.);
format EligStart     EligStop    LockinStart     LockinStop date9. ;
datalines;
ABC1  09/02/2010 10/14/2010 04/16/2010    03/29/2012
run;

data want;
  set have;
  ineligstart=lockinstart; ineligstop=eligstart-1; output;
  ineligstart=eligstop+1;  ineligstop=lockinstop;  output;
  format ineligstart ineligstop date9.;
run;

If these are not your rules, then you have to clarify.

Re: How to find ineligible date range from a date range

erbmani — Thu, 09 Feb 2017 06:26:25 GMT

Here are some more example for better understanding of expected results.

PARTCIPAN, Elig.Start Dt,Elig.stop.Dt,Lockin start Dt, Lockin Stop Dt

ABC1,09/02/2010,10/14/2010,04/16/2010,03/29/2012

ABC2,08/23/2010,09/01/2010,04/16/2010,03/29/2012

ABC3,12/01/2009,08/22/2010,04/16/2010,03/29/2012
Expected output:
ABC1,09/02/2010,10/14/2010,04/16/2010,03/29/2012,04/16/2010,09/01/2010 *Apr162010 till sep 1st is out of eilg. date range

ABC1,09/02/2010,10/14/2010,04/16/2010,03/29/2012,10/15/2010,03/29/2012 *though its lockin partial covered but since Oct15

till Mar29 are out of elig. date range.

ABC2,08/23/2010,09/01/2010,04/16/2010,03/29/2012,04/16/2010,08/22/2010
ABC2,08/23/2010,09/01/2010,04/16/2010,03/29/2012,09/02/2010,03/29/2012

ABC3,12/01/2009,08/22/2010,04/16/2010,03/29/2012,08/23/2010,03/29/2012

*Comments

Re: How to find ineligible date range from a date range

erbmani — Thu, 09 Feb 2017 06:28:48 GMT

Its extract from DB2 table. its fine to have either format mmddyy10 or $10.

Re: How to find ineligible date range from a date range

mkeintz — Thu, 09 Feb 2017 16:56:54 GMT

I offered some tentative rules, which prompted more examples on your part. I do not find your example output helpful in understanding how those rules should be modified.

Please state the rules in words.

Re: How to find ineligible date range from a date range

erbmani — Thu, 09 Feb 2017 18:28:08 GMT

We concern about below cases ONLY,

Case1:

When Lockin_start < elig_ start and Lockin_Stop <=elig_stop

then

we report this record since part of Lockin date range is outside elig_start range.

so it will be like this,

Ineligible_start_date =Lockin_start_date; Ineligible_stop_date=Elig_start - 1;

Example Case#1

INPUT;

ABC1,09/02/2010,10/14/2010,04/16/2010,09/29/2010
OUTPUT:One record of Ineligible time frame.

04/16/2010,09/01/2010

Case2:

When Lockin_start >= elig_ start and Lockin_Stop > elig_stop

then we are reporting this record since part of Lockin_stop date is outside elig_stop date.

so it will be,

Ineligible_start_date=Elig_stop_date+1; Ineligible_stop_date=Lockin_Stop_date;

Example Case#2

INPUT;

ABC1,12/01/2009,08/22/2010,04/16/2010,03/29/2012
OUTPUT:One record of Ineligible time frame.

08/23/2010,03/29/2012

Case3:

When Lockin_start < elig_ start and Lockin_Stop > elig_stop

Here BOTH Lockin_start and Lockin_stop has partial date range outside the eligibility period.

so this has to be handled by two records;

Ineligible_start_date= Lockin_start_date; Ineligible_stop_date=Elig_start_date - 1;

Ineligible_start_date= Elig_stop_date+1; Ineligible_stop_ date=Lockin_Stop_date;

Example Case#3

INPUT;

ABC1,08/23/2010,09/01/2010,04/16/2010,03/29/2012
OUTPUT:Two records of Ineligible time frames.
04/16/2010,08/22/2010
09/02/2010,03/29/2012

Inelig_startdt_01 Inelig_stopdt_01 Inelig_startdt_02 Inelig_stopdt_02

04/16/2010,08/22/2010 09/02/2010,03/29/2012

Re: How to find ineligible date range from a date range

mkeintz — Fri, 10 Feb 2017 20:00:07 GMT

Thank you. Now that the rules are clear to me, I can almost directly translate them into SAS code:

data have;

input Participant :$4. EligStart EligStop LockinStart LockinStop) (:mmddyy10.);

format EligStart EligStop LockinStart LockinStop date9. ;

datalines;

ABC1 09/02/2010 10/14/2010 04/16/2010 03/29/2012

ABC2 08/23/2010 09/01/2010 04/16/2010 03/29/2012

ABC3 12/01/2009 08/22/2010 04/16/2010 03/29/2012

run;

data want;

set have;

format ineligstart ineligstop date9.;

if lockinstart<eligstart and lockinstop<=eligstop then do;

ineligstart=lockinstart; ineligstop=eligstart-1; output;

end; else

if lockinstart>=eligstart and lockinstop>eligstop then do;

ineligstart=eligstop+1; ineligstop=lockinstop; output;

end; else

if lockinstart<eligstart and lockinstop>eligstop then do;

ineligstart=lockinstart; ineligstop=eligstart-1; output;

ineligstart=eligstop+1; ineligstop=lockinstop; output;

end;

run;

But now that I understand that all you want is are date ranges in which lockinstart/lockinstop is not within the interval eligstart/eligstop, the code can be simplified. Instead of looking for three conditions (excess on left only, on right only, and on both sides), you can just look for excess on left, and on right, as in:

data want2;

set have;

format ineligstart ineligstop date9.;

if lockinstart<eligstart then do;

ineligstart=lockinstart;

ineligstop=eligstart-1;

output;

end;

if lockinstop>eligstop then do;

ineligstart=eligstop+1;

ineligstop=lockinstop;

output;

end;

run;

One note: your samples do not include cases when the lockin range is ENTIRELY to the left or right of the eligible range (i.e. lockinstop<eligstart or lockinstart>eligstop). The programs above assume that you want to include those unaccounted for dates in the INELIG date range. But if not, then you should change:

ineligstop=eligstart-1;

ineligstop=min(eligstart-1,lockinstop);

and

ineligstart=eligstop+1;

ineligstart=max(lockinstart,eligstop+1);

Re: How to find ineligible date range from a date range

erbmani — Mon, 17 Apr 2017 23:26:31 GMT

Sorry mkeintz. Focused on other things and finally i got time to revisit to give fix and it worked perfectly. Thanks mkeintz and everyone.