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!
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;
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;
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?
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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.