BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## How to cyclically use past 12-month data only

Hi all,

I currently have 36-month data, January 2020-December 2022, and want to generate monthly report for 24 months (January 2021-December 2022) using 12-month lookback. For example, to generate the report for January 2021, the data of January 2020-January 2021 should be used, and for the report of February 2021, the data of  February 2020-February 2021 can be used.

Could anyone guide me how to cyclically keep only 12-month data for the purpose? The example data is as below. The timeframe can be based on service_beg_dt.

data clmfile;
infile datalines truncover dsd;
input Person_ID clm_beg_dt :mmddyy10. clm_end_dt :mmddyy10. diag_cd \$;
format clm_beg_dt clm_end_dt mmddyy10.;
datalines;
1,1/1/2020,1/1/2020,F02.81
1,6/1/2020,6/7/2020,F03.90
1,1/3/2021,1/3/2021,F03.91
2,2/1/2020,2/5/2020,F04
2,2/6/2020,2/6/2020,F10.27
2,2/4/2021,2/6/2021,F10.96
2,7/2/2022,7/2/2022,F10.97
3,5/26/2021,5/26/2021,G10
3,5/27/2021,5/27/2021,G12.21
3,9/30/2022,10/5/2022,G20
;
run;

Thank you!

Lichee

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: How to cyclically use past 12-month data only

I combined all your suggestions and answers. The code below provides me the desired output for my initial ask. Thank you very much to you all!

%macro reports(year);
%do month=1 %to 12;
%let report_date=%sysfunc(inputn(&month/01/&year,mmddyy10.));
%let report_date_yyyymm=%sysfunc(putn(&report_date.,yymmn6.));
%let beg_dt=%sysfunc(intnx(month,&report_date.,-11));
%let end_dt=%sysfunc(intnx(month,&report_date.,0,E));
data report&report_date_yyyymm.;
set clmfile;
if &beg_dt.<= clm_beg_dt <=&end_dt.;
run;

%end;
%mend;
%reports(2021)

18 REPLIES 18
Diamond | Level 26

## Re: How to cyclically use past 12-month data only

What goes into the report? You talk about 12 month data, but then you give 13 month intervals (February 2020-February 2021).

Depending on what goes into the report, you may be able to use multilabel formats here. As these only work with a few SAS PROCs, other types of reports (such as PROC REPORT) may require either a macro, or a technique similar to "rolling regressions" such as described here or here.

--
Paige Miller
SAS Super FREQ

## Re: How to cyclically use past 12-month data only

Here is one approach using macro to generate individual rolling 12 reports for a year:

``````%macro reports(year);
%do month=1 %to 12;
%let report_date=%sysfunc(inputn(&month/01/&year,mmddyy10.));
title "Report for %qsysfunc(putn(&report_date,monyy.))";
proc print data=clmfile(Where=(clm_beg_dt between &report_date and (intnx('month',&report_date,12)-1)));
run;
title;
%end;
%mend;

options mprint;
%reports(2020)``````
Check out my Jedi SAS Tricks for SAS Users
Quartz | Level 8

## Re: How to cyclically use past 12-month data only

Thanks Jedi! I tested out the data with your code, and it works well.

Could you guide me to generate reports as data files instead of printing out data? Thanks a lot!

Diamond | Level 26

## Re: How to cyclically use past 12-month data only

Instead of PROC PRINT, you create a DATA step that does what you want, with the same WHERE option as used in PROC PRINT.

--
Paige Miller
Quartz | Level 8

## Re: How to cyclically use past 12-month data only

Below is what I tried, but it's not working. Sorry that I'm still learning macro with loops. Thank you!

%macro reports(year);
%do month=1 %to 12;

%let report_date=%sysfunc(inputn(&month/01/&year,mmddyy10.));
%let report_date_yyyymm=%sysfunc(inputn(&month/01/&year,yymmn6.));
data report&report_date_yyyymm.;
set clmfile;
if clm_beg_dt between &report_date and (intnx('month',&report_date,12)-1);
run;
title;
%end;
%mend;

Diamond | Level 26

## Re: How to cyclically use past 12-month data only

It seems as if you have defined the macro but you never execute it. To execute the macro, add this line underneath the macro and then run it

``%reports(2020)``

If that's not the problem, or if it still doesn't work:

To help debug macros, turn on the MPRINT option before you run your macro. You do this by running this command.

``options mprint;``

Then, run your macro and show us the ENTIRE log down to the first ERROR and let's say 10 lines after the first error (do NOT show us just the errors)

--
Paige Miller
Quartz | Level 8

## Re: How to cyclically use past 12-month data only

So I ran the code below:

%macro reports(year);
%do month=1 %to 12;
%let report_date=%sysfunc(inputn(&month/01/&year,mmddyy10.));
%let report_date_yyyymm=%sysfunc(inputn(&month/01/&year,yymmn6.));
data report&report_date_yyyymm.;
set clmfile;
if clm_beg_dt between &report_date and (intnx('month',&report_date,12)-1);
run;
title;
%end;
%mend;

options mprint;
%reports(2020)

The error messages are as below:

MPRINT(REPORTS): data report.;
MPRINT(REPORTS): set clmfile;
SYMBOLGEN: Macro variable REPORT_DATE resolves to 21915
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.

ERROR 201-322: The option is not recognized and will be ignored.

388: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 388-185: Expecting an arithmetic operator.
202: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
3 The SAS System 11:05 Friday, June 2, 2023

SYMBOLGEN: Macro variable REPORT_DATE resolves to 21915
MPRINT(REPORTS): if clm_beg_dt between 21915 and ( intnx('month',21915,12)-1);
MPRINT(REPORTS): run;

NOTE: The SAS System stopped processing this step because of errors.

Diamond | Level 26

## Re: How to cyclically use past 12-month data only

Try this:

``````%macro reports(year);
%do month=1 %to 12;
%let report_date=%sysfunc(inputn(&month/01/&year,mmddyy10.));
/* Next line was changed */
%let report_date_yyyymm=%sysfunc(putn(&report_date,yymmn6.));
data report&report_date_yyyymm;
set clmfile;
if clm_beg_dt between &report_date and (intnx('month',&report_date,12)-1);
run;
title;
%end;
%mend;``````
--
Paige Miller
Quartz | Level 8

## Re: How to cyclically use past 12-month data only

Thank you so much! But the same error messages show up again:

MPRINT(REPORTS): data report202001;
MPRINT(REPORTS): set clmfile;
SYMBOLGEN: Macro variable REPORT_DATE resolves to 21915
388: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 388-185: Expecting an arithmetic operator.
202: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
SYMBOLGEN: Macro variable REPORT_DATE resolves to 21915
MPRINT(REPORTS): if clm_beg_dt between 21915 and ( intnx('month',21915,12)-1);
MPRINT(REPORTS): run;

Diamond | Level 26

## Re: How to cyclically use past 12-month data only

I am not getting any errors. Please re-start SAS and see if the code works.

--
Paige Miller
Quartz | Level 8

## Re: How to cyclically use past 12-month data only

I restarted SAS and used the code below, but still got error message.

Code:

data clmfile;
infile datalines truncover dsd;
input Person_ID clm_beg_dt :mmddyy10. clm_end_dt :mmddyy10. diag_cd \$;
format clm_beg_dt clm_end_dt mmddyy10.;
datalines;
1,1/1/2020,1/1/2020,F02.81
1,6/1/2020,6/7/2020,F03.90
1,1/3/2021,1/3/2021,F03.91
2,2/1/2020,2/5/2020,F04
2,2/6/2020,2/6/2020,F10.27
2,2/4/2021,2/6/2021,F10.96
2,7/2/2022,7/2/2022,F10.97
3,5/26/2021,5/26/2021,G10
3,5/27/2021,5/27/2021,G12.21
3,9/30/2022,10/5/2022,G20
4,5/26/2021,5/26/2021,F03.90
4,5/27/2021,5/27/2021,F03.91
4,9/30/2022,10/5/2022,F04
;
run;

options mprint;

%macro reports(year);
%do month=1 %to 12;
%let report_date=%sysfunc(inputn(&month/01/&year.,mmddyy10.));
/* Next line was changed */
%let report_date_yyyymm=%sysfunc(putn(&report_date.,yymmn6.));
data report&report_date_yyyymm.;
set clmfile;
if clm_beg_dt between &report_date and (intnx('month',&report_date,12)-1);
run;
%end;
%mend;
%reports(2020)

Log error message:

MPRINT(REPORTS): data report202001;
MPRINT(REPORTS): set clmfile;
388: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 388-185: Expecting an arithmetic operator.
202: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
MPRINT(REPORTS): if clm_beg_dt between 21915 and ( intnx('month',21915,12)-1);
MPRINT(REPORTS): run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2023-06-11T16:34:22,563-04:00| _DISARM| WorkspaceServer| _DISARM| |
_DISARM| | _DISARM| | _DISARM| 19492864| _DISARM| 10| _DISARM| 11| _DISARM| 3584| _DISARM| 14056203| _DISARM| | _DISARM| |
_DISARM| | _DISARM| | _DISARM| | _DISARM| | _ENDDISARM
WARNING: The data set WORK.REPORT202001 may be incomplete. When this step was stopped there were 0 observations and 4 variables.

Diamond | Level 26

## Re: How to cyclically use past 12-month data only

BETWEEN does not work in the IF statement here.

Use this:

``if &report_date <= clm_beg_dt <= (intnx('month',&report_date,12)-1);``
--
Paige Miller
Quartz | Level 8

## Re: How to cyclically use past 12-month data only

This works well!

However, the timeframe if &report_date. <= clm_beg_dt <= (intnx('month',&report_date.,12)-1) is actually looking ahead, instead of looking back. I'm trying to use 11-month lookback as below:

%macro reports(year);
%do month=1 %to 12;
%let report_date=%sysfunc(inputn(&month/01/&year,mmddyy10.));
%let report_date_yyyymm=%sysfunc(putn(&report_date.,yymmn6.));
%let beg_dt=%sysfunc(putn(intnx('month',&report_date.,-11),mmddyy10.));
%let end_dt=%sysfunc(putn(intnx('month',&report_date.,0,'E'),mmddyy10.));

data report&report_date_yyyymm.;
set clmfile;
if &beg_dt.<= clm_beg_dt <=&end_dt.;
run;
title;
%end;
%mend;
%reports(2021)

There  are error messages as below:

SYMBOLGEN: Macro variable REPORT_DATE resolves to 22281
ERROR: Argument 1 to function PUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC
or %QSYSFUNC function reference is terminated.
MLOGIC(REPORTS): %LET (variable name is END_DT)
SYMBOLGEN: Macro variable REPORT_DATE resolves to 22281
ERROR: Required operator not found in expression: intnx('month',22281,0,'E')
ERROR: Argument 1 to function PUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC
or %QSYSFUNC function reference is terminated.
SYMBOLGEN: Macro variable REPORT_DATE_YYYYMM resolves to 202101
MPRINT(REPORTS): data report202101;
MPRINT(REPORTS): set clmfile;
SYMBOLGEN: Macro variable BEG_DT resolves to
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, INPUT, PUT.
SYMBOLGEN: Macro variable END_DT resolves to
22: LINE and COLUMN cannot be determined.
3 The SAS System 23:12 Saturday, June 10, 2023

NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, bitstring, INPUT, PUT.
MPRINT(REPORTS): if <= clm_beg_dt <= ;
MPRINT(REPORTS): run;

Quartz | Level 8

## Re: How to cyclically use past 12-month data only

11-month lookback is specifically to have the past 11-month data and data of the current month. For example, when the report month is January 2021, I wanted to keep the data of February 2020 to January 2021, for February 2021, to keep data of March 2020 to February 2021,. etc..

Thank a lot!

Discussion stats
• 18 replies
• 440 views
• 4 likes
• 4 in conversation