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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.