Computing row average of a column conditioned on area

[Raza_M]

Hi SAS Community,

I need help in calculating 3 months and 4 months temperature average for different years by area. Months are November, December, January, and February. These months are not from the same year but November and December are from previous year, for example November and December from year 2010 and January and February from 2011. Three months average would be of (temperature_3month_avg_2011 =)November 2010, December 2010, and January 2011 and four months average (temperature_4month_avg_2011 = ) adds February 2011. It will be the same for the rest of the years. These should be grouped by area-code.

data temp;
input area_code $ month $ year $ monthly_avg_temperature ;
datalines;
110 11 2010 -8.0
110 12 2010 -12.03
110 1  2011 -1.40
110 2  2011  -20.13
110 11 2011 -11.07
110 12 2011 -15.20
110 1  2012  -5.07
110 2  2012 -5.6
110 11  2012 -5.8
110 12  2012 -6.0
110  1  2013 -1.48
110 2   2013 -0.44
110 11 2013 1.74
110 12 2013 2.9
110 1 2014 3.52
110 2 2014 7.3
111 11 2010 3.66
111 12 2010 11.05
111 1 2011 9.25
111 2 2011 12.34
111 11 2011 16.78
111 12 2011 18.52
111 1 2012 17.95
111 2 2012 17.82
111 11 2012 15.82
111 12 2012 19.44
111 1 2013 16.3
111 2 2013 13.75
111 11 2013 14.75
111 12 2013 12.18
111 1 2014 10.12
111 2  2014 11.84
112 11 2010 14.96
112  12 2010 12.48
112  1 2011 7.34
112  2 2011 7.5
112  11 2011 9.50
112  12 2011 7.75
112  1 2012 3.84
112  2 2012 1.42
112  11 2012 4.29
112  12 2012 5.05
112  1 2013 2.52
112  2 2013 -4.7
112  11 2013 0.14
112  12 2013 -2.48
112  1 2014 -7.2
112  2 2014 -5.85
;

The Output data would be like this 

data temp;
input area_code $ temperature_3month_avg_2011$ temperature_3month_avg_2012 $ temperature_3month_avg_2013$ temperature_3month_avg_2014;
datalines;
110 -7.41 -10.44 -4.42 2.71
111
112
;

 There will be a row for each area. It is fine if three and four month average variables are in two separate datasets, I can merge using area code. 

Any help is highly appreciated and if there are any questions, I am happy to answer.

[PaigeMiller · Accepted solution]

Important advice: always (that's 100% of the time, no exceptions) work with calendar and clock values, such as YEAR and MONTH, as numeric variables, not character variables.

data temp;
input area_code $ month year monthly_avg_temperature ;
datalines;
110 11 2010 -8.0
110 12 2010 -12.03
110 1  2011 -1.40
110 2  2011  -20.13
110 11 2011 -11.07
110 12 2011 -15.20
110 1  2012  -5.07
110 2  2012 -5.6
110 11  2012 -5.8
110 12  2012 -6.0
110  1  2013 -1.48
110 2   2013 -0.44
110 11 2013 1.74
110 12 2013 2.9
110 1 2014 3.52
110 2 2014 7.3
111 11 2010 3.66
111 12 2010 11.05
111 1 2011 9.25
111 2 2011 12.34
111 11 2011 16.78
111 12 2011 18.52
111 1 2012 17.95
111 2 2012 17.82
111 11 2012 15.82
111 12 2012 19.44
111 1 2013 16.3
111 2 2013 13.75
111 11 2013 14.75
111 12 2013 12.18
111 1 2014 10.12
111 2  2014 11.84
112 11 2010 14.96
112  12 2010 12.48
112  1 2011 7.34
112  2 2011 7.5
112  11 2011 9.50
112  12 2011 7.75
112  1 2012 3.84
112  2 2012 1.42
112  11 2012 4.29
112  12 2012 5.05
112  1 2013 2.52
112  2 2013 -4.7
112  11 2013 0.14
112  12 2013 -2.48
112  1 2014 -7.2
112  2 2014 -5.85
;
data temp1;
    set temp;
    if month in (11,12) then year=year+1;
run;
proc summary data=temp1(where=(month in (11,12,1))) nway;
    class area_code year; 
    var monthly_avg_temperature;
    output out=temperature_3month_avg mean=;
run;
proc summary data=temp1(where=(month in (11,12,1,2))) nway;
    class area_code year; 
    var monthly_avg_temperature;
    output out=temperature_4month_avg mean=;
run;