How to cyclically use past 12-month data only

[lichee]

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

[PaigeMiller]

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.

[SASJedi]

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)

[lichee]

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!

[PaigeMiller]

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

[lichee]

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;

[PaigeMiller]

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)

[lichee]

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.

[PaigeMiller]

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;

[lichee]

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;

[PaigeMiller]

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

[lichee]

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.

[PaigeMiller]

BETWEEN does not work in the IF statement here.

Use this:

if &report_date <= clm_beg_dt <= (intnx('month',&report_date,12)-1);

[lichee]

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;

[lichee]

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!