## Calculating Concentration Index

Solved
Occasional Contributor
Posts: 17

# Calculating Concentration Index

Hi Everyone, thanks for the help so far. I have one question here.

I need to calculate a concentration index (H). Basically, each company has multiple departments each year and each department has their own revenue.

Question: I need, for each year, each company, calculating H, where H is the sum of squares for each department's revenue divided by the company's total revenue. For example, for company A is 1999, I need (20/100)^2 + (15/100)^2 +(30/100)^2 + (12/100)^2.

However, I need to apply a criteria first. If you were to take a look at company B in year 2000. The total department revenue is higher than the company's total revenue. That cannot be the case. (It would be fine if company revenue > department revenue because not all departments are reported). I would need to delete all observations in that particular year.

Thanks for the help!

 Company Company revenue Department Department revenue Year A 100 1 20 1999 A 100 2 15 1999 A 100 3 30 1999 A 100 4 12 1999 A 130 1 20 2000 A 130 2 30 2000 A 130 3 40 2000 A 130 4 10 2000 A 130 5 20 2000 B 200 1 100 1999 B 200 2 20 1999 B 200 3 60 1999 B 400 1 200 2000 B 400 2 200 2000 B 400 3 100 2000

Accepted Solutions
Solution
‎08-20-2012 10:43 PM
Respected Advisor
Posts: 3,167

## Re: Calculating Concentration Index

Posted in reply to rilatotoro

Here is SQL quickie (not necessarily faster, just easily coding), meanwhile, data step may have a performance edge:

data have;

input Company\$    Company_revenue    Department\$    Department_revenue    Year;

cards;

A    100    1    20    1999

A    100    2    15    1999

A    100    3    30    1999

A    100    4    12    1999

A    130    1    20    2000

A    130    2    30    2000

A    130    3    40    2000

A    130    4    10    2000

A    130    5    20    2000

B    200    1    100    1999

B    200    2    20    1999

B    200    3    60    1999

B    400    1    200    2000

B    400    2    200    2000

B    400    3    100    2000

;

proc sql noprint;

create table want as

select *, sum((department_revenue/company_revenue)**2) as H_index from have group by     company,year having sum(department_revenue)<=company_revenue;

quit;

Haikuo

Update:

/*DOW data step approach:*/

data want;

do until (last.year);

set have;

by company year;

H_index+(department_revenue/company_revenue)**2;

dr+department_revenue;

end;

if dr<=company_revenue then flag=1;

do until (last.year);

set have;

by company year;

if flag=1 then output;

end;

call missing(H_index,dr);

drop dr flag;

run;

/*data step: stack*/

data want;

set have (in=up) have;

by company year;

if first.year then call missing(h_index,dr);

if up then do;

H_index+(department_revenue/company_revenue)**2;

dr+department_revenue;

end;

if not up and dr<=company_revenue then output;

drop dr;

run;

All Replies
Solution
‎08-20-2012 10:43 PM
Respected Advisor
Posts: 3,167

## Re: Calculating Concentration Index

Posted in reply to rilatotoro

Here is SQL quickie (not necessarily faster, just easily coding), meanwhile, data step may have a performance edge:

data have;

input Company\$    Company_revenue    Department\$    Department_revenue    Year;

cards;

A    100    1    20    1999

A    100    2    15    1999

A    100    3    30    1999

A    100    4    12    1999

A    130    1    20    2000

A    130    2    30    2000

A    130    3    40    2000

A    130    4    10    2000

A    130    5    20    2000

B    200    1    100    1999

B    200    2    20    1999

B    200    3    60    1999

B    400    1    200    2000

B    400    2    200    2000

B    400    3    100    2000

;

proc sql noprint;

create table want as

select *, sum((department_revenue/company_revenue)**2) as H_index from have group by     company,year having sum(department_revenue)<=company_revenue;

quit;

Haikuo

Update:

/*DOW data step approach:*/

data want;

do until (last.year);

set have;

by company year;

H_index+(department_revenue/company_revenue)**2;

dr+department_revenue;

end;

if dr<=company_revenue then flag=1;

do until (last.year);

set have;

by company year;

if flag=1 then output;

end;

call missing(H_index,dr);

drop dr flag;

run;

/*data step: stack*/

data want;

set have (in=up) have;

by company year;

if first.year then call missing(h_index,dr);

if up then do;

H_index+(department_revenue/company_revenue)**2;

dr+department_revenue;

end;

if not up and dr<=company_revenue then output;

drop dr;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 1 reply
• 842 views
• 0 likes
• 2 in conversation