How to create columns showing average value of rows while grouping by variables

[AzamonTuscomer]

Dear all:

I'm a beginner user of SAS and struggling with constructing a database. The original one looks like:

OFTIC   TICKER   MEASURE   VALUE  ACTUAL  ANNDATS_ACT   

    A              1            EPS              0.1        0.9          2015/01/30

    A              1            EPS              0.5        0.9          2015/01/30

    A              1            EPS              0.8        0.9          2015/01/30

    A              1            BPS              0.5        0.8          2015/01/30

    A              1            BPS              0.4        0.8          2015/01/30

    A              1            BPS              0.2        0.8          2015/01/30

    A              1            CPS              1.5        3.5          2015/01/30

    A              1            CPS              2.8        3.5          2015/01/30

    A              1            CPS              5.5        3.5          2015/01/30

I would like to add columns that show the mean VALUE and the ACTUAL of each measure grouping by OFTIC, TICKER, ANNDATS_ACT. The table I want to create looks like: 

OFTIC   TICKER  ANNDATS_ACT  EPS_AVG   EPS_ACTUAL   BPS_AVG   BPS_ACTUAL   

   A               1          2015/01/30              0.466              0.9                0.366              0.8                  

CPS_AVG   CPS_ACTUAL 

      3.266               3.5 

I'm trying to make it through just dividing the original database by MEASURE and merging them. However, there are over 24 different measures and thousands of observations. 

Could anybody give a piece of advice to me? Any comments would be highly appreciated. 

Thank you. 

[novinosrin · Accepted solution]

Hi @AzamonTuscomer   Looks fairly straight forward

 
data have;
input (OFTIC   TICKER   MEASURE) ($)   VALUE  ACTUAL  ANNDATS_ACT   :yymmdd10.;
format 	ANNDATS_ACT   yymmdd10.;
cards;
    A              1            EPS              0.1        0.9          2015/01/30
    A              1            EPS              0.5        0.9          2015/01/30
    A              1            EPS              0.8        0.9          2015/01/30
    A              1            BPS              0.5        0.8          2015/01/30
    A              1            BPS              0.4        0.8          2015/01/30
    A              1            BPS              0.2        0.8          2015/01/30
    A              1            CPS              1.5        3.5          2015/01/30
    A              1            CPS              2.8        3.5          2015/01/30
    A              1            CPS              5.5        3.5          2015/01/30
	;

proc summary data=have nway noprint;
class OFTIC   TICKER   MEASURE     ACTUAL ANNDATS_ACT ;
var value;
output out=temp(drop=_:) mean=AVG;
run;

proc transpose data=temp out=temp2;
by OFTIC   TICKER   MEASURE	ANNDATS_ACT notsorted;
var actual avg;
run;

proc transpose data=temp2 out=want(drop=_:) delim=_;
by OFTIC   TICKER   	ANNDATS_ACT notsorted;
var col1;
id measure _name_;
run;

[AzamonTuscomer]

Thank you so much for your help, novinosrin! I really appreciate it.