Merge output of proc means with the original data

shalmali — Tue, 04 Dec 2012 19:36:17 GMT

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

Re: Merge output of proc means with the original data

Astounding — Tue, 04 Dec 2012 19:47:34 GMT

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.

Re: Merge output of proc means with the original data

shalmali — Sun, 09 Dec 2012 21:44:28 GMT

Thanks for the prompt reply. Your code does work.

topic Re: Merge output of proc means with the original data in SAS Programming

Merge output of proc means with the original data

Re: Merge output of proc means with the original data

Re: Merge output of proc means with the original data