topic Re: How to cyclically use past 12-month data only in SAS Programming

How to cyclically use past 12-month data only

lichee — Fri, 09 Jun 2023 14:31:14 GMT

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

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

PaigeMiller — Fri, 09 Jun 2023 14:55:14 GMT

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.

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

SASJedi — Fri, 09 Jun 2023 15:01:43 GMT

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)

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

Reeza — Fri, 09 Jun 2023 15:16:20 GMT

Please show your desired output if this is your data. Because you're not just straight summing I assume, is it a count of the diag_cd across those time periods? Unique persons?

Depending on the metric, the approach may differ.

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

lichee — Fri, 09 Jun 2023 16:30:11 GMT

I hope it makes sense. Thank you all!

I shall add a few more records in the dummy data. The desired reports are reportYYYYMM. In the reports I wanted to

1) list out person_ids with eligible diagnosis code F03.90, F03.91 in the past 12 months,

2) count number of times a person had the eligible diagnoses in the past 12 months, and

3) include the first date when a person had the eligible diagnosis code in the past 12 months.

So person_id=1 was reported in the report of January 2021 (report202101), and two individuals (person_ids 1 and 4) were reported with eligible diagnosis code in May 2021(report202105) . For the report of June 2021(report202106), person_id=1 is only found to have the eligible diagnosis once in the past 12 months, so the report of the month changes from the previous month. So is the report of January 2022 (report202201). The tricky part is that each monthly report should equally use past 12-month data.

Thank you very much!

data report202101;
infile datalines truncover dsd;
input Person_ID dx_cnt first_eligible_dx_date :mmddyy10.;
format first_eligible_dx_date mmddyy10.;
datalines;
1,2,6/1/2020
;
run;
data report202102;
infile datalines truncover dsd;
input Person_ID dx_cnt first_eligible_dx_date :mmddyy10.;
format first_eligible_dx_date mmddyy10.;
datalines;
1,2,6/1/2020
;
run;

data report202105;
infile datalines truncover dsd;
input Person_ID dx_cnt first_eligible_dx_date :mmddyy10.;
format first_eligible_dx_date mmddyy10.;
datalines;
1,2,6/1/2020
4,2,5/26/2021
;
run;

data report202106;
infile datalines truncover dsd;
input Person_ID dx_cnt first_eligible_dx_date :mmddyy10.;
format first_eligible_dx_date mmddyy10.;
datalines;
1,1,1/3/2021
4,2,5/26/2021
;
run;
data report202107;
infile datalines truncover dsd;
input Person_ID dx_cnt first_eligible_dx_date :mmddyy10.;
format first_eligible_dx_date mmddyy10.;
datalines;
1,1,1/3/2021
4,2,5/26/2021
;
run;

data report202201;
infile datalines truncover dsd;
input Person_ID dx_cnt first_eligible_dx_date :mmddyy10.;
format first_eligible_dx_date mmddyy10.;
datalines;
4,2,5/26/2021
;
run;

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

lichee — Sun, 11 Jun 2023 13:50:58 GMT

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!

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

PaigeMiller — Sun, 11 Jun 2023 14:27:18 GMT

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

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

lichee — Sun, 11 Jun 2023 14:44:11 GMT

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;

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

PaigeMiller — Sun, 11 Jun 2023 16:03:39 GMT

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)

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

lichee — Sun, 11 Jun 2023 17:56:46 GMT

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.

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

PaigeMiller — Sun, 11 Jun 2023 19:00:27 GMT

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;

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

lichee — Sun, 11 Jun 2023 20:02:10 GMT

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;

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

PaigeMiller — Sun, 11 Jun 2023 20:23:27 GMT

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

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

lichee — Sun, 11 Jun 2023 20:36:33 GMT

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

Code:

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;

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

PaigeMiller — Sun, 11 Jun 2023 21:13:59 GMT

BETWEEN does not work in the IF statement here.

Use this:

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

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

lichee — Mon, 12 Jun 2023 02:58:30 GMT

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;

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

lichee — Mon, 12 Jun 2023 03:02:01 GMT

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!

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

PaigeMiller — Mon, 12 Jun 2023 11:54:04 GMT

Inside %SYSFUNC you do not use quotes around text strings. There is no need for PUTN in &BEG_DT and &END_DT.

%let beg_dt=%sysfunc(intnx(month,&report_date.,-11));
%let end_dt=%sysfunc(intnx(month,&report_date.,0,E));

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

lichee — Mon, 12 Jun 2023 20:38:52 GMT

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)