sum and ratio, by year and industry

[redrover99]

Hi all

I am looking to perform the following functions

-Sum  the squared revenues(REVT_SQUARED) by industry (HERFSIC) and year (FYEAR). As there are 7 different industries and 61 different years (1950-2011) there should be approx. 427 total revenues(TOTREV)...

-Divide the firms individual squared revenue (REVT_SQUARED) by the total revenue(TOTREV) for that particular industry (HERFSIC) and year (FYEAR) and call this variable "CONRATIO"

-CONRATIO'S should look like 0.15, 0.34 0.22 etc.

Help on performing this command would be very helpful. I am currently receiving very low concentration scores such as; 0.0003, 0.0012 etc. I am worried there might be an error with my command. data below

[GeoffreyBrent]

I'm a bit confused by "squared revenue" - not obvious why this would be of interest. But I'm assuming TOTREV is supposed to be the sum of squared revenue, not the sum of individual revenue, otherwise the ratio doesn't make much sense either. (You would get different answers depending on whether you worked in dollars or cents.)

Under those assumptions:

proc summary data=have nway;

class herfsic fyear;

var revt_squared;

output out=total_squared_revs (drop=_type_ _freq_) sum(revt_squared)=totrev;

quit;

proc sql;

create table want as select

a.*,

b.totrev,

a.revt_squared/b.totrev as conratio

from have as a join total_squared_revs as b

on (a.herfsic=b.herfsic AND a.fyear=b.fyear);

quit;

[redrover99]

Thanks Geoff

Squared revenue is just the revenue variable^2,

TOTREV is the summation of the squared revenue by year and industry. So your assumptions are correct.

I am following the calculation of the herfindahl index