topic Re: Calculate Herfindahl Index in New SAS User

Calculate Herfindahl Index

dapenDaniel — Mon, 10 Feb 2020 05:41:11 GMT

Hello,

I would like to calculate the Herfindahl index each year. There are three steps.

1. calculate the total industry sales for each year, which is the sum of sales for each firm each year.

2. calculate the market share square for each firm each year. Market_share_sqr = (sales for each firm each year / total industry sale for that year)^2.

3. select the 50 largest market share each year and add them up.

The sample data is below.

FirmID	Year	Sales
1001	1992	1
1001	1993	2
1001	1994	3
1002	1992	4
1002	1993	5
1002	1994	6
1003	1992	7
1003	1993	8
1003	1994	9
1004	1992	10
1004	1993	11
1004	1994	12

Want: Since I have 4 firms here, I only select the larget two market shares. In my complete data, there are more than 200 firms and I would like to select the largest 50 firms each year.

Year	H_Index
1992	0.307851
1993	0.273669
1994	0.25

What program do I need to use? Thanks.

Re: Calculate Herfindahl Index

Norman21 — Mon, 10 Feb 2020 06:06:02 GMT

This question has already been asked - and answered...

https://communities.sas.com/t5/General-SAS-Programming/Calculating-an-Index/td-p/377449

Re: Calculate Herfindahl Index

unison — Mon, 10 Feb 2020 06:08:04 GMT

Here's a way to go about it

data have;
	input firmid year sales;
	datalines;
1001 1992 1
1001 1993 2
1001 1994 3
1002 1992 4
1002 1993 5
1002 1994 6
1003 1992 7
1003 1993 8
1003 1994 9
1004 1992 10
1004 1993 11
1004 1994 12
;
run;

proc sql;
	create table test as select a.firmid, a.year, (a.sales/tot_sales.sales)**2 as 
		market_share_sqr from have a left join (select year, sum(sales) as sales from 
		have group by year) tot_sales on a.year=tot_sales.year order by a.year, 
		market_share_sqr desc;
quit;

proc rank data=test out=ranked(where=(firm_rank<=2)) descending;
	by year;
	var market_share_sqr;
	ranks firm_rank;
run;

proc means nway missing noprint data=ranked;
	class year;
	var market_share_sqr;
	output out=want(drop=_type_ _freq_) sum=;
run;

Change firm_rank<=2 to <=200 if you want top 200.