Select Dates 1 month before/after, regardless of year

[MB_Analyst]

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. 

[Reeza]

Don't you need to provide a month as well? Or does year matter?

[MB_Analyst]

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.

[Reeza]

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. 

[PaigeMiller]

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.

[novinosrin]

I am a little wary of you ID=12 expected results, can you please review "thoroughly" and confirm 

13

1

2018/01/03

[novinosrin · Accepted solution]

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;

[Ksharp]

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;