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

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));
--
Paige Miller
Reeza
Super User
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.
lichee
Quartz | Level 8

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;

 

lichee
Quartz | Level 8

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)

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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