How to find ineligible date range from a date range

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How to find ineligible date range from a date range

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

 

 


Accepted Solutions
Solution
‎04-17-2017 07:25 PM
Trusted Advisor
Posts: 1,019

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

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;

to

   ineligstop=min(eligstart-1,lockinstop);

 

and

   ineligstart=eligstop+1;

to

   ineligstart=max(lockinstart,eligstop+1);

 

 

 

View solution in original post


All Replies
Super User
Posts: 19,789

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

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

Super User
Posts: 11,343

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

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.

Occasional Contributor
Posts: 5

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

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

Trusted Advisor
Posts: 1,019

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

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.

Occasional Contributor
Posts: 5

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

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

Trusted Advisor
Posts: 1,019

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

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.

Occasional Contributor
Posts: 5

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

[ Edited ]

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
OUTPUTSmiley Surprisedne 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
OUTPUTSmiley Surprisedne 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

OR

Inelig_startdt_01 Inelig_stopdt_01 Inelig_startdt_02 Inelig_stopdt_02

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

Solution
‎04-17-2017 07:25 PM
Trusted Advisor
Posts: 1,019

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

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;

to

   ineligstop=min(eligstart-1,lockinstop);

 

and

   ineligstart=eligstop+1;

to

   ineligstart=max(lockinstart,eligstop+1);

 

 

 

Occasional Contributor
Posts: 5

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

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

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

Discussion stats
  • 9 replies
  • 176 views
  • 1 like
  • 4 in conversation