topic Re: Find AVG for states in year/month in New SAS User

Find AVG for states in year/month

Noob314 — Fri, 26 Apr 2019 20:09:05 GMT

I got one columb that’s in yearsmonth , one column of states and another column for spending. I want to take avg spending for a state in a year and start a new column/variable with the info in it.

Ex
State_fips state_pc yearmonth spend
2. AK. 201202
3 AK. 201201
2. SD 201203
3. SD. 201304
3

Re: Find AVG for states in year/month

ballardw — Fri, 26 Apr 2019 20:43:57 GMT

First step in almost anything involving dates is to ensure that you have an actual SAS date valued numeric variable. Then there are a slew of tools to work with.

Best is to share what your actual data set is like using a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

You need to show what your desired output looks like. It is not clear whether you want a data set, for further processing, or a report that people will read.

Re: Find AVG for states in year/month

Reeza — Sat, 27 Apr 2019 22:34:28 GMT

You need to use PROC MEANS most likely. It would be helpful if you provided more details, specifically what you want the output to look like. You may need to create a new variable or you can use some tricks to change the YearMonth variable into a Year value.

Here's an example of how PROC MEANS would work. If you check my other examples, there's one that shows how to add the mean to the main data set.
https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

Re: Find AVG for states in year/month

smijoss1 — Sun, 28 Apr 2019 01:56:05 GMT

you mean something like

proc sql ;
    select state, substr(yearmonth,1,4) as year,  avg(Spend_Amt) as Avg_spend_amt
  From table 
  group by state, substr(yearmonth,1,4);
Quit ;

Re: Find AVG for states in year/month

Reeza — Sun, 28 Apr 2019 02:05:55 GMT

What if YearMonth is numeric?

Re: Find AVG for states in year/month

smijoss1 — Sun, 28 Apr 2019 08:26:05 GMT

Then use substr(put(