About bijayadhikar

bijayadhikar · ‎02-07-2024

Noted. Thanks @mkeintz

bijayadhikar · ‎02-06-2024

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

bijayadhikar · ‎02-06-2024

Here is my revised desired output. 5-year average year-to-date COUNT = all 5 Jan/5

bijayadhikar · ‎02-05-2024

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/surveillance-report-infectious-diseases-jan-dec-2022.pdf?rev=5549ab2c6b25405cbcf7caa18e07fb96&sc_lang=en

bijayadhikar · ‎02-05-2024

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

bijayadhikar · ‎01-05-2024

Thank you to @Kurt for quick response and solution. Appreciated.

bijayadhikar · ‎01-05-2024

Thank you to @yabwon for quick response and solution. Appreciated.

bijayadhikar · ‎01-05-2024

Hi, is there a way to insert date after "Date Extraction:" below, proc odstext; p 'Source:'/ style=[color=blue fontsize=12pt fontfamily='Courier New' fontweight=bold]; list; item 'Master Database, Date Extraction: ' / style=[fontsize=11pt fontfamily='Courier New']; end; run;

bijayadhikar · ‎10-30-2023

Thanks for your time and prompt response!

bijayadhikar · ‎10-30-2023

This should be an easy solution but could not figured it out. How to I select both duplicate IDs if year is associated with 2023? Have ID year 130177 2021 130177 2022 137939 2021 137939 2023 160200 2022 160200 2023 166898 2020 166898 2023 175553 2022 175553 2023 want: ID year 137939 2021 137939 2023 160200 2022 160200 2023 166898 2020 166898 2023 175553 2022 175553 2023 data HAVE; input ID year; cards; 130177 2021 130177 2022 137939 2021 137939 2023 160200 2022 160200 2023 166898 2020 166898 2023 175553 2022 175553 2023 run;

bijayadhikar · ‎10-29-2023

Hi, I would like to calculate days difference from lagging dates if subject_ID appears more than 1 times. e.g. ID 1 appears twice, so I would like to calculate number of days from 2nd infection date (i.e. 15-Apr-15) to 1st treatment date (i.e. 31-Mar-15) =15. If there is only one ID, days=. subject sub_id infection_date treatmet_date days 1 11 1jan2015 31mar2015 . 1 12 15apr2015 30jun2015 15 2 22 1Sep2015 28feb2015 63 3 33 1jan2015 28feb2015 . 3 34 15mar2015 31mar2015 15 3 35 1may2015 30jun2015 31 4 44 1mar2015 31mar2015 . 5 55 1mar2015 31mar2015 5 56 1oct2015 30jun2015 184 SAS dataset: data have; input subject sub_id (infection_date treatmet_date) (: $20.); cards; 1 11 1jan2015 31mar2015 1 12 15apr2015 30jun2015 2 22 1Sep2015 28feb2015 3 33 1jan2015 28feb2015 3 34 15mar2015 31mar2015 3 35 1may2015 30jun2015 4 44 1mar2015 31mar2015 5 55 1mar2015 31mar2015 5 56 1oct2015 30jun2015 ; run; Thanks in advance

bijayadhikar · ‎05-11-2023

@PaigeMiller First the use of 27 months is required in our public health practice and epidemiological view point. The row 2 can be ignored due to prior data (<2018) not available to calculate mean, though we could calculate mean for rows 3 and 4, but I wanted to start at row 5, where I highlighted in green. Data for row 13 (i.e. when we reach Feb 2024) will be available by adding the year 2023 (5 year block from 2019-2023), and Feb 2024 becomes the current month to compare. This is called rolling average. The program I am writing will be automated runs once a month off the database, so it will sends out alert when current month counts is higher than expected. @FreelanceReinh I did run your script. It did produce a desired result for now. I am not sure if I will have any issue going into next year, i.e. 2024. The 5-years block will be from 2019-2023, and current year will be 2024. Regarding missing values, I will insert missing month and missing values as 'zero'. For example for disease A, no data in July, I will insert 0 for that month. Technically zero is a value (i.e. zero cases reported). I am leaning towards accepting as solution in couple of days after few checks.

bijayadhikar · ‎05-10-2023

Thanks @PaigeMiller for further follow up. The logics I am applying here is that for disease aberration detection, we apply historical weekly (or monthly) mean as a threshold (i.e. expected value) to compare with the current week (or month) count to say whether current week (or month) disease count is above or below the threshold. More historical data we use, the better expected value we can get. The 27 months mean: The use of 25 months as a means of center moving average around current month (i.e. April), plus the previous 2 month of current month. This month's disease occurrence is influenced by what happened past couple of months. Fig 2 in this article described a concept. https://downloads.regulations.gov/FDA-2011-N-0731-0001/attachment_3.pdf The figure 2 is what I am trying to create for each week. In my example, for each month. Does this answer your question?

bijayadhikar · ‎05-10-2023

Thank you @FreelanceReinh . I am going to try your proposed script. @PaigeMiller, thanks you. about your question, please find little bit detail about what I was trying to accomplish. Although I did not include standard deviation calc in my original ask, I will be using SD as mentioned in the excel formula

bijayadhikar · ‎05-10-2023

Hi all, I am looking for your help on how to compute 5 year's monthly moving average to compare with the current month. Mean number of cases is based on the past two months of the current year and the current month ± 2 months for 5 previous years (27 months). See illustration in the picture below, which how the yellow colored cells (27 months) are used in moving average to compare with current month, green cell. Data sets is below. data have; input Disease $ YEAR Month count; datalines; A 2018 1 19 A 2019 1 25 A 2020 1 15 A 2021 1 6 A 2022 1 18 A 2023 1 6 A 2018 2 22 A 2019 2 9 A 2020 2 21 A 2021 2 3 A 2022 2 8 A 2023 2 19 A 2018 3 20 A 2019 3 15 A 2020 3 5 A 2021 3 9 A 2022 3 9 A 2023 3 18 A 2018 4 11 A 2019 4 10 A 2021 4 1 A 2022 4 8 A 2023 4 15 A 2018 5 14 A 2019 5 16 A 2020 5 9 A 2021 5 4 A 2022 5 10 A 2018 6 19 A 2019 6 15 A 2020 6 8 A 2021 6 6 A 2022 6 26 A 2018 7 21 A 2019 7 12 A 2020 7 42 A 2021 7 16 A 2022 7 12 A 2018 8 15 A 2019 8 15 A 2020 8 13 A 2021 8 13 A 2022 8 8 A 2018 9 9 A 2019 9 8 A 2020 9 4 A 2021 9 11 A 2022 9 4 A 2018 10 12 A 2019 10 10 A 2020 10 5 A 2021 10 11 A 2022 10 12 A 2018 11 7 A 2019 11 15 A 2020 11 5 A 2021 11 6 A 2022 11 7 A 2018 12 22 A 2019 12 13 A 2020 12 7 A 2021 12 20 A 2022 12 14 B 2018 1 7 B 2019 1 7 B 2020 1 9 B 2021 1 8 B 2022 1 8 B 2023 1 13 B 2018 2 9 B 2019 2 11 B 2020 2 16 B 2021 2 5 B 2022 2 4 B 2023 2 11 B 2018 3 13 B 2019 3 8 B 2020 3 6 B 2021 3 9 B 2022 3 11 B 2023 3 19 B 2018 4 13 B 2019 4 17 B 2020 4 5 B 2021 4 11 B 2022 4 14 B 2023 4 10 B 2018 5 17 B 2019 5 12 B 2020 5 13 B 2021 5 13 B 2022 5 20 B 2023 5 1 B 2018 6 26 B 2019 6 32 B 2020 6 23 B 2021 6 21 B 2022 6 24 B 2018 7 24 B 2019 7 30 B 2020 7 31 B 2021 7 34 B 2022 7 22 B 2018 8 16 B 2019 8 18 B 2020 8 24 B 2021 8 15 B 2022 8 13 B 2018 9 8 B 2019 9 23 B 2020 9 19 B 2021 9 8 B 2022 9 16 B 2018 10 8 B 2019 10 13 B 2020 10 14 B 2021 10 8 B 2022 10 11 B 2018 11 16 B 2019 11 13 B 2020 11 6 B 2021 11 8 B 2022 11 12 B 2018 12 12 B 2019 12 10 B 2020 12 3 B 2021 12 10 B 2022 12 9 ; run;

Online Status	Offline
Date Last Visited	‎02-11-2024 12:57 PM

Re: calculate YTD for moving month vs current year YTD by disease

Re: calculate YTD for moving month vs current year YTD by disease

Re: calculate YTD for moving month vs current year YTD by disease

Re: calculate YTD for moving month vs current year YTD by disease

calculate YTD for moving month vs current year YTD by disease

Re: How to insert date in odstext

Re: How to insert date in odstext

How to insert date in odstext

Re: How to select both duplicate IDs if a condition met

How to select both duplicate IDs if a condition met

Re: How to select both duplicate IDs if a condition met

Re: calculate days from lagging dates

Re: Custom datestamp in the output filename

Re: Custom datestamp in the output filename

Re: Need help in SGPLOT title with ODS

Re: calculate YTD for moving month vs current year YTD by disease

Re: How to select both duplicate IDs if a condition met

Re: calculate YTD for moving month vs current year YTD by disease

Re: calculate YTD for moving month vs current year YTD by disease

Re: calculate YTD for moving month vs current year YTD by disease

Re: calculate YTD for moving month vs current year YTD by disease

calculate YTD for moving month vs current year YTD by disease

Re: How to insert date in odstext

Re: How to insert date in odstext

How to insert date in odstext

Re: How to select both duplicate IDs if a condition met

How to select both duplicate IDs if a condition met

calculate days from lagging dates

Re: How to compute monthly moving average using 5 years data by group

Re: How to compute monthly moving average using 5 years data by group

Re: How to compute monthly moving average using 5 years data by group

How to compute monthly moving average using 5 years data by group