topic Finding date in a list of ranges in SAS Programming

Finding date in a list of ranges

Belle — Mon, 21 Sep 2020 19:24:39 GMT

Hello,

I am struggling on finding a list of dates that are in the certain date range. Two datasets are many to many. Any way to do this?

Thanks in advance.

The final result should be look like below.

ID	SVC_DT
123	05Jan2019
123	20Aug2019
456	02Jul2019
567	03Sep2019
567	04Dec2019

Here is my two datasets:

data Have;
input ID $ DATE :mmddyy10.;
format date date9.;
cards;
123 12/04/2018
123 01/05/2019
123 08/20/2019
123 12/04/2019
456 07/01/2019
456 04/02/2019
567 09/03/2019
567 12/04/2019
567 03/02/2019
;
run;

data Range;
input ID $ START :mmddyy10. END :mmddyy10.;
format START END date9.;
cards;
123 10/01/2018 03/03/2019
123 05/01/2019 09/30/3019
456 05/01/2019 09/30/3019
567 07/01/2019 10/31/2019
567 11/01/2019 12/31/2019
;
run;

Re: Finding date in a list of ranges

Reeza — Mon, 21 Sep 2020 19:30:12 GMT

SQL joins are a good option here because you can use BETWEEN. It includes the end points in the interval though, if you need to exclude those dates you 'll need to change the join logic.

proc sql;
create table want as
select t1.*
from have as t1
join range as t2
on t1.id=t2.id
and t1.date between t2.start and t2.end
order by 1,2;
quit;

@Belle wrote:

Hello,

I am struggling on finding a list of dates that are in the certain date range. Two datasets are many to many. Any way to do this?

Thanks in advance.

The final result should be look like below.

ID SVC_DT

123 05Jan2019

123 20Aug2019

456 02Jul2019

567 03Sep2019

567 04Dec2019

Here is my two datasets:

1.

data Have;
input ID $ DATE :mmddyy10.;
format date date9.;
cards;
123 12/04/2018
123 01/05/2019
123 08/20/2019
123 12/04/2019
456 07/01/2019
456 04/02/2019
567 09/03/2019
567 12/04/2019
567 03/02/2019
;
run;

2.

data Range;
input ID $ START :mmddyy10. END :mmddyy10.;
format START END date9.;
cards;
123 10/01/2018 03/03/2019
123 05/01/2019 09/30/3019
456 05/01/2019 09/30/3019
567 07/01/2019 10/31/2019
567 11/01/2019 12/31/2019
;
run;

Re: Finding date in a list of ranges

Belle — Mon, 21 Sep 2020 20:00:55 GMT

Hi Reeza,

Thanks for your reply.

I ran but it kept ID 123 on date 12/04/2019 which is not in the range. Any other solution?

Thanks again.

Re: Finding date in a list of ranges

Belle — Mon, 21 Sep 2020 20:05:24 GMT

Hi Reeza,

It works, it was typo, should be 2019 instead of 3019.

Thanks again