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;
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;
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;
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.
Hi Reeza,
It works, it was typo, should be 2019 instead of 3019.
Thanks again
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: