topic Select Dates 1 month before/after, regardless of year in SAS Programming

Select Dates 1 month before/after, regardless of year

MB_Analyst — Thu, 05 Dec 2019 18:01:54 GMT

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.

Re: Select Dates 1 month before/after, regardless of year

Reeza — Thu, 05 Dec 2019 18:15:50 GMT

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

Re: Select Dates 1 month before/after, regardless of year

MB_Analyst — Thu, 05 Dec 2019 18:17:14 GMT

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.

Re: Select Dates 1 month before/after, regardless of year

Reeza — Thu, 05 Dec 2019 18:20:33 GMT

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.

Re: Select Dates 1 month before/after, regardless of year

PaigeMiller — Thu, 05 Dec 2019 18:49:56 GMT

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.

Re: Select Dates 1 month before/after, regardless of year

novinosrin — Thu, 05 Dec 2019 18:47:07 GMT

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

2018/01/03

Re: Select Dates 1 month before/after, regardless of year

novinosrin — Thu, 05 Dec 2019 19:04:32 GMT

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;

Re: Select Dates 1 month before/after, regardless of year

Ksharp — Fri, 06 Dec 2019 12:27:16 GMT

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;