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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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);

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

9 REPLIES 9
Reeza
Super User

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

ballardw
Super User

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.

erbmani
Calcite | Level 5

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

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
erbmani
Calcite | Level 5

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

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
erbmani
Calcite | Level 5

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

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

mkeintz
PROC Star

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);

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
erbmani
Calcite | Level 5
Sorry mkeintz. Focused on other things and finally i got time to revisit to give fix and it worked perfectly. Thanks mkeintz and everyone.

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
  • 9 replies
  • 957 views
  • 1 like
  • 4 in conversation