topic Re: Calculating Month and Fiscal Averages in SAS Visual Analytics

Calculating Month and Fiscal Averages

ChristyN — Wed, 16 Dec 2020 22:57:08 GMT

Have the following in SAS VA *8.3 I believe*:

date	population	gender
04Jan2015	395	F
04Jan2015	3052	M
05Jan2015	3143	M
05Jan2015	397	F
06Jan2015	405	F
06Jan2015	3120	M
07Jan2015	3022	M
07Jan2015	378	F
08Jan2015	3193	M
08Jan2015	390	F
09Jan2015	390	F
09Jan2015	3144	M
10Jan2015	3114	M
10Jan2015	370	F
11Jan2015	402	F
11Jan2015	3455	M
12Jan2015	386	F

Want:

a) average population per month

b) average population per fiscal year (April 01-March 31)

Re: Calculating Month and Fiscal Averages

PetriRoine — Mon, 21 Dec 2020 10:02:41 GMT

Could you please confirm the date variable in your example data is correct? It's now all January 2015. Could you please also explain what you have tried? List Table, Crosstab, bar chart,...?

Best regards,

Petri

PS. You can check the exact version number from top right corner: click the User icon > About.

Re: Calculating Month and Fiscal Averages

jaseit — Tue, 05 Jan 2021 19:42:00 GMT

Asssuming your date variable is in a date format, you can do the following:

(If your date is a character, you can create a calculated item and use the parse operator to convert the date to a date type)

Average by Month:

1. Duplicate the date variable, rename to Month Year, and change the format to 'MMMYYYY'

2. Create a calculated item 'Average Population' = Avg [by group] population'

3. Put the Month Year and Average Population in a list table and averages by month will be calculated.

Average by Fiscal Year:

I would recommend adding an extra column in your data indicating which fiscal period that date falls within. For example, if one fiscal year is April 2014 - March 2015, then add an extra column where the fiscal year is 2014 for all those records. Your data should look something like this:

date	population	gender	fiscal year
04Jan2015	395	F	2014
04Jan2015	3052	M	2014
05Apr2015	3143	M	2015
05May2015	397	F	2015
06Jan2016	405	F	2015
06May2016	3120	M	2016

Then, you can use the fiscal year variable and the same average population item created above in another list table and the averages will automatically calculate for the fiscal years.

Hope this helps,

Jaime