Calculating Concentration Index

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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!

CompanyCompany revenueDepartmentDepartment revenueYear
A1001201999
A1002151999
A1003301999
A1004121999
A1301202000
A1302302000
A1303402000
A1304102000
A1305202000
B20011001999
B2002201999
B2003601999
B40012002000
B40022002000
B40031002000

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

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;

View solution in original post


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

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
  • 682 views
  • 0 likes
  • 2 in conversation