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! ... View more

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; ... View more

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. ... View more

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;   ... View more

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. ... View more

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; ... View more

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! ... View more

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 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; 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;   ... View more

Sorry! Data have should be as below:   data have; infile datalines truncover dsd; input Person_ID var1 $ var2 $ var3 $ var4; datalines; 1,aa,aa1,aaa, ,,aa2,,10 2,bb,bb,bbb,6 3,cc,cc,ccc,8 4,dd,dd,ddd, 5,ee,ee,eee,7 6,ff,ff,fff, 7,gg,gg1,ggg,3 ,,gg2,,5 ,,gg3,,6 8,hh,hh1,hhh, ,,hh2,, ,,hh3,, ; run;   Thank you! ... View more

The dummy data would look like below. Basically only Person_ID and var1 need to be populated (as from data have to data want), but not var3 or var4 even thought they have missing values as well. data have; infile datalines truncover dsd; input Person_ID var1 $ var2 $ var3 $ var4; datalines; ID,var1,var2,var3,var4 1,aa,aa1,aaa, ,,aa2,,10 2,bb,bb,bbb,6 3,cc,cc,ccc,8 4,dd,dd,ddd, 5,ee,ee,eee,7 6,ff,ff,fff, 7,gg,gg1,ggg,3 ,,gg2,,5 ,,gg3,,6 8,hh,hh1,hhh, ,,hh2,, ,,hh3,, ; run;   data want; infile datalines truncover dsd; input Person_ID var1 $ var2 $ var3 $ var4; datalines; 1,aa,aa1,aaa, 1,aa,aa2,,10 2,bb,bb,bbb,6 3,cc,cc,ccc,8 4,dd,dd,ddd, 5,ee,ee,eee,7 6,ff,ff,fff, 7,gg,gg1,ggg,3 7,gg,gg2,,5 7,gg,gg3,,6 8,hh,hh1,hhh, 8,hh,hh2,, 8,hh,hh3,, ; run;   ... View more

Thanks a lot! The code works for the dummy data. What if there are many other fields with missing values but other fields do not need to be populated for their missing values in the same way? For example, if I only wanted to populate var1 but not var2, can we specify fields to be populated? Thanks again! ... View more