BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Takdir
Obsidian | Level 7

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.

firmsales yearAverage sales
A240020012600
A250020022600
A260020032600
A270020042600
A280020052600
B150020011600
B160020021600
B170020031600
C310020013250
C320020023250
C330020033250
C340020043250
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20
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;
neveragain
Calcite | Level 5

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

mkeintz
PROC Star

@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

to

select firm, avg(sales) as average_sales

 

and drop the 

order by firm,year

clause.

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
neveragain
Calcite | Level 5

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

mkeintz
PROC Star

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

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
PaigeMiller
Diamond | Level 26
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.

 

--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 18393 views
  • 1 like
  • 5 in conversation