Find AVG for states in year/month

[Noob314]

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

[ballardw]

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-dat... 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.

[Reeza]

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

[smijoss1]

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 ;

[Reeza]

What if YearMonth is numeric?

[smijoss1]

Then use substr(put(