topic Re: How to create columns showing average value of rows while grouping by variables in New SAS User

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

AzamonTuscomer — Mon, 02 Sep 2019 00:38:00 GMT

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.

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

novinosrin — Mon, 02 Sep 2019 01:06:52 GMT

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;

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

AzamonTuscomer — Mon, 02 Sep 2019 01:56:53 GMT

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