topic Re: Overlapping dates in SAS Programming

Overlapping dates

luvscandy27 — Tue, 04 Feb 2020 22:34:49 GMT

Hello, the code for the sample data set I am working with is listed below.

data test1;
 infile datalines delimiter = ',';
 input id $ reg_date:mmddyy10. startdate:mmddyy10. enddate:mmddyy10. extdate:mmddyy10.  ;
 format  reg_date startdate enddate extdate mmddyy10.;
 datalines;
 100,11/30/2017,11/30/2017,11/30/2018,08/31/2017
 200,11/30/2017,11/30/2017,11/30/2018,08/31/2017
 300,11/30/2017,11/30/2017,11/30/2018,08/31/2017
 ;
 run;

Flag variables are created in the data set with the code below.

 data test2;
 	set test1;
 if startdate le reg_date le enddate then withinyr =1;
 if extdate le reg_date le startdate then withingrace=1;
 if withinyr ne 1 and withingrace ne 1 then out=1;
 
 run;

If/then else was not used to create the flag variables because in certain instances the dates overlap and cause a flag for withinyr and withingrace for the same id as pictured below. I would like to know if there is a way, I can flag just withinyr

when this issue happens?

Obs	id	reg_date	startdate	enddate	extdate	withinyr	withingrace	out
1	100	11/30/2017	11/30/2017	11/30/2018	08/31/2017	1	1	.
2	200	11/30/2017	11/30/2017	11/30/2018	08/31/2017	1	1	.
3	300	11/30/2017	11/30/2017	11/30/2018	08/31/2017	1	1	.

Re: Overlapping dates

PaigeMiller — Tue, 04 Feb 2020 23:17:32 GMT

Your IF statements should look like this:

 if startdate le reg_date and startdate le enddate then withinyr =1;

then these are computed correctly and OUT will be computed correctly as well.

Re: Overlapping dates

Tom — Tue, 04 Feb 2020 23:28:27 GMT

Are you trying to test if two ranges overlap?

Let's make a picture. Say you have one range from A to B and the second range from C to D.

----+---0----+---0----+---0----+---0
  ^        ^
  +-A----B-+
----+---0----+---0----+---0----+---0
      ^        ^
      +-C----D-+

then they overlap when

D>=A and C<=B

The second is contained in the first when

A<=C<=B and A<=D<=B

Re: Overlapping dates

luvscandy27 — Tue, 04 Feb 2020 23:45:56 GMT

Thank you. Just make sure I understand I could write my code like below and it would be correct? And their would be no chance of flagging withinyr and withingrace at the sametime.

if startdate le reg_date and startdate le enddate then withinyr =1;
 	else if  startdate le reg_date and startdate le extdate then withingrace =1;
 		else  out = 1;

Re: Overlapping dates

PGStats — Tue, 04 Feb 2020 23:51:02 GMT

Why not

data test2;
 	set test1;
 if startdate le reg_date le enddate then withinyr =1;
 if extdate le reg_date lt startdate then withingrace=1;
 if withinyr ne 1 and withingrace ne 1 then out=1;
 run;

Re: Overlapping dates

PaigeMiller — Wed, 05 Feb 2020 01:43:59 GMT

@luvscandy27 wrote:

Thank you. Just make sure I understand I could write my code like below and it would be correct? And their would be no chance of flagging withinyr and withingrace at the sametime.
if startdate le reg_date and startdate le enddate then withinyr =1;
 	else if  startdate le reg_date and startdate le extdate then withingrace =1;
 		else  out = 1;

I was not thinking an ELSE was needed here, and I don't know if using ELSE is the right logic or not. That's really something you have to decide. Does it give you the expected answer?