calculate YTD for moving month vs current year YTD by disease

[bijayadhikar]

Hi,

I am trying to calculate last year's YDT (i.e. 2023 Jan), last 5 year's YTD (i.e. 2019-2023 Jan), last 5 year's annual average (i.e. average 2019-2023 Jan) and compare with this year's YTD counts (i.e. 2024 Jan) to see if this year's YTD counts are high.  Once we have Feb full data we will calc YTD accordingly. Here is the dataset, thanks in advance for your help.

DATA SHOWN ONLY FOR 3 MONTHS HERE

data have;
infile datalines delimiter=',';
input disease$ year month tot;

cards;
A,2019,1,7
A,2019,2,9
A,2019,3,13
A,2020,1,7
A,2020,2,10
A,2020,3,9
A,2021,1,8
A,2021,2,16
A,2021,3,6
A,2022,1,8
A,2022,2,5
A,2022,3,7
A,2023,1,8
A,2023,2,4
A,2023,3,11
B,2019,1,11
B,2019,2,2
B,2019,3,5
B,2020,1,3
B,2020,2,6
B,2020,3,7
B,2021,1,2
B,2021,2,2
B,2021,3,1
B,2022,1,4
B,2022,2,4
B,2022,3,1
B,2023,1,2
B,2023,2,11
B,2023,3,5
A,2024,1,4
B,2024,1,7
;
run;

Expected OUTPUT

[Reeza]

Is your desired shown correctly for the headings? What value does YTD 2019-2023 provide when the period is not same as the average? I would expect that to be the average. And you want to include the average including the current year? or is that a typo?

[bijayadhikar]

Thank you Reeza. Likely I made a mistake. My objective is to produce 2024 monthly report. Each month of 2024 will be compared avg of last 5 years of that month. i.e. JAN this year will be compared with five previous January divided by 5, so I get historical average to compare with this year. Here is an example report I found and trying to produce automated report that I will schedule in SAS Management Console. Basically I need to figure out 2nd last column of the report below using macro year and month, so that the it will be good next year and so on. You can ignore other YTD columns. I need "5-year average year-to-date COUNT" column. 

https://www.publichealthontario.ca/-/media/Documents/Surveillance-Reports/Infectious/2022/surveillan... 

[bijayadhikar]

Here is my revised desired output.

5-year average year-to-date COUNT = all 5 Jan/5

[Reeza · Accepted solution]

Assuming you have no missing data this is one way. If you do have missing data, you will need another method.

Edited to reflect @mkeintz comment (thanks!)

proc sort data=have;
    by disease month year;
run;

proc transpose data=have out=wide prefix=YEAR;
    by disease month;
    id year;
    var tot;
run;

data want;
    set wide;
    by disease month;
    retain running_total;
     if first.disease then running_total=0;
    total=sum(of year2019-year2023);
    running_total+total;
    YTD_AVG_2019_2023=running_total/5;
    drop running_total total _name_;
run;

---

@bijayadhikar wrote:

Here is my revised desired output.

5-year average year-to-date COUNT = all 5 Jan/5

---

[bijayadhikar]

Thank you @Reeza  This works for me. Very apricated.

[mkeintz]

I think a

if first.disease then call missing(running_total) ;

statement is needed to prevent including prior diseases in the ytd values for the current disease.

[bijayadhikar]

Noted. Thanks @mkeintz