I have data with a date column, and I would like to filter only rows that are within 1 month (before and after) of a specified month - but doing this over each year. Input data is:
ID | Month | DATE |
1 | 1 | 2019/01/03 |
2 | 2 | 2019/02/20 |
3 | 3 | 2019/03/21 |
4 | 4 | 2019/04/25 |
5 | 5 | 2019/05/12 |
6 | 6 | 2019/06/14 |
7 | 7 | 2019/07/01 |
8 | 8 | 2019/08/03 |
9 | 9 | 2019/09/20 |
10 | 10 | 2019/10/21 |
11 | 11 | 2019/11/25 |
12 | 12 | 2019/12/12 |
13 | 1 | 2018/01/03 |
14 | 2 | 2018/02/20 |
15 | 3 | 2018/03/21 |
16 | 4 | 2018/04/25 |
17 | 5 | 2018/05/12 |
18 | 6 | 2018/06/14 |
19 | 7 | 2018/07/01 |
20 | 8 | 2018/08/03 |
21 | 9 | 2018/09/20 |
22 | 10 | 2018/10/21 |
23 | 11 | 2018/11/25 |
24 | 12 | 2018/12/12 |
25 | 1 | 2017/01/22 |
If I were to specify month=3, I would want to keep:
ID | Month | DATE |
2 | 2 | 2019/02/20 |
3 | 3 | 2019/03/21 |
4 | 4 | 2019/04/25 |
14 | 2 | 2018/02/20 |
15 | 3 | 2018/03/21 |
16 | 4 | 2018/04/25 |
And if I were to specify month=12:
ID | Month | DATE |
1 | 1 | 2019/01/03 |
11 | 11 | 2019/11/25 |
12 | 12 | 2019/12/12 |
13 | 1 | 2018/01/03 |
23 | 11 | 2018/11/25 |
24 | 12 | 2018/12/12 |
25 | 1 | 2017/01/22 |
I'm not sure how to do this by only specifying the month to use. The first example I can simply say month+1 and month-1, but when it's the year change, I'm unsure.
Should my assumption hold true,
data have;
input ID Month DATE :yymmdd10.;
format date yymmdd10.;
cards;
1 1 2019/01/03
2 2 2019/02/20
3 3 2019/03/21
4 4 2019/04/25
5 5 2019/05/12
6 6 2019/06/14
7 7 2019/07/01
8 8 2019/08/03
9 9 2019/09/20
10 10 2019/10/21
11 11 2019/11/25
12 12 2019/12/12
13 1 2018/01/03
14 2 2018/02/20
15 3 2018/03/21
16 4 2018/04/25
17 5 2018/05/12
18 6 2018/06/14
19 7 2018/07/01
20 8 2018/08/03
21 9 2018/09/20
22 10 2018/10/21
23 11 2018/11/25
24 12 2018/12/12
25 1 2017/01/22
;
proc sql;
create table want as
select b.*,a.month as a_month /*a.month is needed in the output for tests,of course you can drop once you understand*/
from have a inner join have b
on intnx('month',a.date,-1,'b')<=b.date<=intnx('month',a.date,1,'e')
order by a.month,b.id;
quit;
Year doesn't matter, I just want to select all months within a one month boundary. I can't use month-1 and month+1 if I select month=12.
where month(date) =month( intnx('month', mdy(&monthParameter, 1, 2019), 1, 'b')) or month(date) = month( intnx('month', mdy(&monthParameter, -1, 2019), 1, 'b'))
Use INTNX is one option. I suspect you could also use MOD() in some fashion.
As @Reeza has pointed out, using actual SAS date values (which are integers representing the number of days since January 1, 1960) and actual SAS date functions (such as INTNX and INTCK and MDY and many others) is the way to go. SAS has already done the hard work to program what month comes after December, and what month comes before January, and which years are leap years, and so on and so forth, so you won't have to code these calendar matters yourself.
I am a little wary of you ID=12 expected results, can you please review "thoroughly" and confirm
13 | 1 | 2018/01/03 |
Should my assumption hold true,
data have;
input ID Month DATE :yymmdd10.;
format date yymmdd10.;
cards;
1 1 2019/01/03
2 2 2019/02/20
3 3 2019/03/21
4 4 2019/04/25
5 5 2019/05/12
6 6 2019/06/14
7 7 2019/07/01
8 8 2019/08/03
9 9 2019/09/20
10 10 2019/10/21
11 11 2019/11/25
12 12 2019/12/12
13 1 2018/01/03
14 2 2018/02/20
15 3 2018/03/21
16 4 2018/04/25
17 5 2018/05/12
18 6 2018/06/14
19 7 2018/07/01
20 8 2018/08/03
21 9 2018/09/20
22 10 2018/10/21
23 11 2018/11/25
24 12 2018/12/12
25 1 2017/01/22
;
proc sql;
create table want as
select b.*,a.month as a_month /*a.month is needed in the output for tests,of course you can drop once you understand*/
from have a inner join have b
on intnx('month',a.date,-1,'b')<=b.date<=intnx('month',a.date,1,'e')
order by a.month,b.id;
quit;
data have;
input ID Month DATE :yymmdd10.;
format date yymmdd10.;
cards;
1 1 2019/01/03
2 2 2019/02/20
3 3 2019/03/21
4 4 2019/04/25
5 5 2019/05/12
6 6 2019/06/14
7 7 2019/07/01
8 8 2019/08/03
9 9 2019/09/20
10 10 2019/10/21
11 11 2019/11/25
12 12 2019/12/12
13 1 2018/01/03
14 2 2018/02/20
15 3 2018/03/21
16 4 2018/04/25
17 5 2018/05/12
18 6 2018/06/14
19 7 2018/07/01
20 8 2018/08/03
21 9 2018/09/20
22 10 2018/10/21
23 11 2018/11/25
24 12 2018/12/12
25 1 2017/01/22
;
%let month=12;
%let month_before=%sysfunc(ifn(&month=1,12,%eval(&month-1)));
%let month_after =%sysfunc(ifn(&month=12,1,%eval(&month+1)));
%put &month &month_before &month_after ;
data want;
set have;
if month in ( &month &month_before &month_after );
run;
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 25. 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.