topic Re: How do I calculate average by grouping data ? in New SAS User

How do I calculate average by grouping data ?

Takdir — Thu, 15 Nov 2018 02:35:18 GMT

data compustat;
input firm sales year;

datalines;
A 2400 2001
A 2500 2002
A 2600 2003
A 2700 2004
A 2800 2005
B 1500 2001
B 1600 2002
B 1700 2003
C 3100 2001
C 3200 2002
C 3300 2003
C 3400 2004
;

I want a column with average for each group of firm for every sales. can someone please help with that ? here as an example of the output.

firm	sales	year	Average sales
A	2400	2001	2600
A	2500	2002	2600
A	2600	2003	2600
A	2700	2004	2600
A	2800	2005	2600
B	1500	2001	1600
B	1600	2002	1600
B	1700	2003	1600
C	3100	2001	3250
C	3200	2002	3250
C	3300	2003	3250
C	3400	2004	3250

Re: How do I calculate average by grouping data ?

novinosrin — Thu, 15 Nov 2018 02:47:45 GMT

data compustat;
input firm $ sales year;

datalines;
A 2400 2001
A 2500 2002
A 2600 2003
A 2700 2004
A 2800 2005
B 1500 2001
B 1600 2002
B 1700 2003
C 3100 2001
C 3200 2002
C 3300 2003
C 3400 2004
;

proc sql;
create table want as
select *, avg(sales) as average_sales
from compustat
group by firm
order by firm,year;
quit;

Re: How do I calculate average by grouping data ?

neveragain — Thu, 25 Feb 2021 00:04:22 GMT

One follow-up question...how would the code change if you only wanted to group by Firm and only show the average for each firm grouping once? See the end result is below.

Firm average_sales

A 2600

B 1600

C 3250

Re: How do I calculate average by grouping data ?

mkeintz — Thu, 25 Feb 2021 01:30:18 GMT

@neveragain wrote:

One follow-up question...how would the code change if you only wanted to group by Firm and only show the average for each firm grouping once? See the end result is below.

Firm average_sales

A 2600

B 1600

C 3250

Then remove implicit or explicit references to year and/or sales in the result table by changing

select *, avg(sales) as average_sales

select firm, avg(sales) as average_sales

and drop the

order by firm,year

clause.

Re: How do I calculate average by grouping data ?

neveragain — Thu, 25 Feb 2021 02:58:00 GMT

Excellent! Thank you for the help. I just thought of another question...what if I wanted to derive the standard deviation or another calculation? End result below:

Firm average_sales standard deviation

A 2600 158

B 1600 100

C 3250 129

Re: How do I calculate average by grouping data ?

mkeintz — Thu, 25 Feb 2021 06:27:10 GMT

"One followup question"

...

"I just thought of another question"

This is not a good habit for a person with the userid @neveragain .

I think it's time to start researching the documentation on functions, which you can apply in a similar way to @novinosrin 's use of the AVG function.

Re: How do I calculate average by grouping data ?

PaigeMiller — Thu, 25 Feb 2021 13:06:34 GMT

proc summary data=compustat nway;
    class firm;
    var sales;
    output out=_stats_ mean=average stddev=std;
run;
data want;
    merge compustat _stats_;
    by firm;
run;

The data set _stats_ that is created is the one you asked for with one record per firm.