Merge output of proc means with the original data

[shalmali]

Hi All,

I have a data set with the name of board of directors for fiscal year 1999. I want to do create four new variables: %outside_director, %inside_director, %outside_total, %inside_total. %outside_director and %inside_director is the proportion of outside directors and inside directors, respectively. %outside_total is the proportion of outside director for the entire sample. Similarly, %inside_total is the proportion of inside director for the entire sample. Subset of my data looks as follows:

Firm Director Status Inside     Outside

A     Sam     I          1               0
A     Tom     I          1               0

A     Jack    O          0               1

A     Tim     O          0               1

A     Mike    O          0               1

B     John     O          0               1

B     Mark     I          1               0

B     Jim    O             0               1

B     Cal     O            0               1

B     Sarf     I            1              0

(I= inside director, O=outside director)

To get %outside_director, %inside_director, I used

proc means data= have n sum;

by firm;

var inside outside;

output out= temp1 n=num sum= sinside soutside;

run; quit;

data temp1; set temp1;

%inside_director= sinside/num;

%outside_director= soutside/num;

run;

data temp2;

merge have temp1;

by firm;

run;

To get %outside_total, %inside_total, I used

proc means data= have n sum;

var inside outside;

output out= temp3 n=num sum= sinside soutside;

run; quit;

data temp3; set temp3;

%inside_total= sinside/num;

%outside_total= soutside/num;

run;

Temp3 does not have any firm identifier. How should I merge this dataset with the other set to get the following output?

Firm Director Status Inside     Outside   %outside_director  %inside_director  %outside_total  %inside_total

A     Sam     I          1               0          0.6                         0.4               0.66                              0.33
A     Tom     I          1               0           0.6                         0.4               0.66                              0.33     

A     Jack    O          0               1          0.6                         0.4               0.66                              0.33

A     Tim     O          0               1          0.6                         0.4               0.66                              0.33

A     Mike    O          0               1          0.6                         0.4               0.66                              0.33

B     John     O          0               1          0.75                    0.25               0.66                              0.33

B     Mark     I          1               0           0.75                    0.25               0.66                              0.33

B     Jim    O             0               1          0.75                    0.25               0.66                              0.33

B     Cal     O            0               1          0.75                    0.25               0.66                              0.33

[Astounding]

shalmali,

First, a couple of corrections / simplifications ...

SAS won't let you create variable names that begin with %.

To get the values for % outside director, etc., you don't need to compute sums and Ns.  The MEAN of INSIDE / OUTSIDE will get you the numbers you are looking for.

A single PROC MEANS can calculate all the needed values.  It will be tricky to understand what the parts are and how to combine them, unless you print out the output data set.

Here's some (untested) code that assumes HAVE is already sorted by FIRM:

proc means data=have;

   class firm;

   var inside outside;

   output out=stats (keep=firm inside_director outside_director _type_) mean=inside_director outside_director;

run;

data want;

   if _n_=1 then set stats (where=(_type_=0) rename=(inside_director=inside_total outside_director=outside_total));

   merge have stats (where=(_type_=1));

   by firm;

run;

Good luck.

[shalmali]

Thanks for the prompt reply. Your code does work.