data number; input com cat tenure ceo_dummy ind_dummy; datalines; 1 1 5 0 1 1 1 7 1 0 1 1 5.5 0 1 1 1 3 0 1 2 1 8 0 1 2 1 2 0 1 2 1 7 1 0 1 2 10 0 1 1 2 8 0 1 1 2 6 1 0 ; run; proc sql; create table want as select a.com,a.cat,sum(a.tenure gt b.tenure) as count from (select * from number where ind_dummy=1) as a, (select * from number where ceo_dummy=1) as b where a.com=b.com and a.cat=b.cat group by a.com,a.cat; quit;
Xia Keshan
Or if your have table is not too big then below SQL would be an alternative to what proposes.
data have;
infile datalines truncover dsd dlm='09'x;
input company:$1. date:ddmmyy10. director_id $ tenure:8. ceo_dummy:$1. non_ceo_dummy:$1.;
format date date9.;
datalines;
A 31/12/2010 2 7 0 1
A 31/12/2010 3 5.5 0 1
A 31/12/2010 1 5 1 0
A 31/12/2010 4 3 0 1
B 31/12/2010 1 5 1 0
B 31/12/2010 4 3 0 1
C 31/12/2010 1 5 1 0
run;
proc sql feedback;
create table want as
select
company,
date,
coalesce
(
(select count(*) from have i where i.company=o.company and i.date=o.date and i.tenure>o.tenure group by i.company,i.date)
,0
)
as total_dir
from
have o
where ceo_dummy='1'
;
quit;
Just re-configure your data so that you have the ceo's tenure and the non-ceo's tenure on the same observation. Then it is easy to count.
data have;
input company $ date director_id tenure ceo_dummy non_ceo_dummy;
informat date ddmmyy10.;
format date yymmdd10.;
cards;
A 31/12/2010 1 5 1 0
A 31/12/2010 2 7 0 1
A 31/12/2010 3 5.5 0 1
A 31/12/2010 4 3 0 1
;
proc sql ;
create table analysis as
select a.company
, a.date
, a.director_id as ceo
, a.tenure as ceo_tenure
, b.director_id as non_ceo
, b.tenure as non_ceo_tenure
from have a
, have b
where a.company = b.company
and a.date = b.date
and a.ceo_dummy = 1
and b.non_ceo_dummy = 1
;
create table want as
select company
, date
, sum(non_ceo_tenure>ceo_tenure) as num_longer
from analysis
group by 1,2
;
quit;
Late for the party, but thought 2XDOW is really efficient if you have large data. Raw data was borrowed from correct answer post.
data number;
input com cat tenure ceo_dummy ind_dummy;
datalines;
1 1 5 0 1
1 1 7 1 0
1 1 5.5 0 1
1 1 3 0 1
2 1 8 0 1
2 1 2 0 1
2 1 7 1 0
1 2 10 0 1
1 2 8 0 1
1 2 6 1 0
;
run;
data want_ds;
call missing(count, _ceo_ten);
do until (last.cat);
set number;
by com cat notsorted;
if ceo_dummy = 1 then
_ceo_ten=tenure;
end;
do until (last.cat);
set number;
by com cat notsorted;
if not missing (_ceo_ten) then
count+tenure >_ceo_ten;
end;
keep com cat count;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.