help with summing up firms in an industry

[shalmali]

Hello All,

I would highly appreciate if someone tell me how I can sum up the firms in an industry and then take the inverse of the sum. My data looks as follows:

firm year industry

A     2001     OIL

B     2001     OIL

C     2001     OIL

D     2001     OIL

A     2002     OIL

B     2002     OIL

C     2002     OIL

U     2001     SERVICE

V     2001    SERVICE

W     2001     SERVICE

X    2001     SERVICE

Y     2002     SERVICE

U     2002     SERVICE

Z     2002     SERVICE

I want the output to look like

firm year industry               SUM     invese

A     2001     OIL                 4          1/4 =0.25

B     2001     OIL               4          0.25

C     2001     OIL               4          0.25

D     2001     OIL               4          0.25

A     2002     OIL               4          0.25

B     2002     OIL               4          0.25

C     2002     OIL               4          0.25

U     2001     SERVICE     6          0.17

V     2001    SERVICE     6          0.17

W     2001     SERVICE     6          0.17

X    2001     SERVICE     6          0.17

Y     2002     SERVICE     6          0.17

U     2002     SERVICE     6          0.17

Z     2002     SERVICE     6          0.17

Thank you.

[art297 · Accepted solution]

You could use:

data have;

  input firm $ year industry $;

  cards;

A     2001     OIL

B     2001     OIL

C     2001     OIL

D     2001     OIL

A     2002     OIL

B     2002     OIL

C     2002     OIL

U     2001     SERVICE

V     2001    SERVICE

W     2001     SERVICE

X    2001     SERVICE

Y     2002     SERVICE

U     2002     SERVICE

Z     2002     SERVICE

;

proc sql;

  create table want as

    select *,

       count(distinct firm) as count,

         1/calculated count as inverse

      from have

        group by industry

  ;

quit;