SAS VA comparing two dynamic time periods

[MVras]

Hi everyone,

I hope I can explain this as easy as possible, but it may be challenging.

What I want to do in VA is comparing our number of ambulance dispatches in 2019 with the same number in 2018 leading up to a specific date. For example I want both years to show the period from January 1st to July 1st and want them to update as I get more data from 2019. I want them visualized as Key Values.

The example below is our ambulance dispatches for 2019.

I guess I have to make some kind of parameter, but I can't figure out how, and that's why I've come on here. 

Regards

Martin

[SASKiwi]

What I would do is create a "relative year counter" in my source data. I would do this in Base SAS before loading into VA. The counter would be simply set to 0 for the current year (2019), 1 for last year (2018), 2 for the year before that (2017) and so on. Assuming you have your data date-stamped - I'm calling it Reporting_Date - this statement should do the trick:

Relative_Year_Counter = intck('YEAR', Reporting_Date, today());

Then to do any yearly comparisons, just filter on 0 for current year, and 1 for last year. It doesn't matter what year you are in, as the relative year will stay the same.

[MVras]

Thank you so much for your reply.

As I can see with your code, 2019 counts right, but 2018 and 2017 count all 365 days and not the same timeperiod as 2019.

I don't know if my wish is more accurate with this example:

2019 - data from January 1st to July 1st

2018 - data from January 1st to July 1st

2017 - data from January 1st to July 1st

With our live data it should update every day/hour and make the comparison much more relevant.

/Martin

[SASKiwi]

Ah, OK. I had assumed you were counting whole calendar years. How about something like this then? Calculate the number of days in the current year and in the reporting year. Then if the days in the reporting year is less than or equal to the number of days in the current year, then assign the counter. Days where the counter is missing will be ignored in your VA filter

Days_Current_Year = today() - mdy(1, 1, year(today()))+1;
Days_Reporting_Year = today() - mdy(1, 1, year(Reporting_Date))+1;
if Days_Reporting_Year <= Days_Current_Year then
  Relative_Year_Counter = intck('YEAR', Reporting_Date, today());

[MVras]

That makes sense. Days_Reporting_Year is counting all dates though. It doesn't group the counter on years.

/Martin

[SASKiwi · Accepted solution]

Now that I've tested this properly and corrected it try this:

data test;
input Reporting_Date :date9.;
format Reporting_Date date9.; 
Days_Current_Year = today() - mdy(1, 1, year(today()))+1;
Days_Reporting_Year = Reporting_Date - mdy(1, 1, year(Reporting_Date))+1;
if Days_Reporting_Year <= Days_Current_Year then
  Relative_Year_Counter = intck('YEAR', Reporting_Date, today());
put _all_;
cards;
20jul2019
20jul2018
20jul2017
25jul2018
25jul2017
;
run;

[MVras]

That worked like a charm. Thank you so much!

[SASKiwi]

Glad I could help!