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));
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;
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)
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.