topic Re: Filtering dates based off different ID in SAS Programming

Filtering dates based off different ID

Kbug — Thu, 15 Aug 2019 15:46:09 GMT

Hello I am using SAS 9.4 and I am trying to figure out how to delete dates that aren't in my desired date range just for one ID.

For example:

DATA : OLD

ID Date

1 1/2/2018

1 11/23/2017

1 12/12/2017

1 12/6/2017

1 1/16/2018

1 3/31/2018

1 10/25/2017

2 4/25/2017

2 9/26/2017

2 1/13/2018

2 12/1/2017

DATA WANT:

ID Date

1 1/2/2018

1 11/23/2017

1 12/12/2017

1 12/6/2017

2 4/25/2017

2 9/26/2017

2 1/13/2018

2 12/1/2017

I am trying to filter the dates that are not between 11/23/2017 and 1/2/2018 for just ID 1. I do not want ID 2 to change.

I am not sure how to go about this. Should I use an if statement?

Many thanks!

Re: Filtering dates based off different ID

PeterClemmensen — Thu, 15 Aug 2019 15:53:33 GMT

One way

data have;
input ID Date : mmddyy10.;
format Date mmddyy10.;
datalines;
1 1/2/2018
1 11/23/2017
1 12/12/2017
1 12/6/2017
1 1/16/2018
1 3/31/2018
1 10/25/2017
2 4/25/2017
2 9/26/2017
2 1/13/2018
2 12/1/2017
;

data want;
	set have;
	if ID=1 and (Date lt '23nov2017'd | Date gt '02jan2018'd)  then delete;
run;

Re: Filtering dates based off different ID

Kbug — Thu, 15 Aug 2019 16:12:19 GMT

It ends up deleting all of ID dates, even if they fall in between the range. What does the lt and the gt mean in the if statement?

Re: Filtering dates based off different ID

novinosrin — Thu, 15 Aug 2019 16:14:33 GMT

Hello @Kbug The solution is in your description "I am trying to filter the dates that are not between 11/23/2017 and 1/2/2018 for just ID 1. I do not want ID 2 to change." i.e Using between and logical operator

data have;
input ID Date : mmddyy10.;
format Date mmddyy10.;
datalines;
1 1/2/2018
1 11/23/2017
1 12/12/2017
1 12/6/2017
1 1/16/2018
1 3/31/2018
1 10/25/2017
2 4/25/2017
2 9/26/2017
2 1/13/2018
2 12/1/2017
;
proc sql;
create table want as
select *
from have
where id=1 and Date between '23nov2017'd  and '02jan2018'd or id ne 1;
quit;