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
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);
What are the rules, how do you know when it starts/stops?
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.
Its extract from DB2 table. its fine to have either format mmddyy10 or $10.
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.
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
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.
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
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);
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.
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.