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
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.
Thanks for the prompt reply. Your code does work.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.