counting the number of observations conditional on another observation

[Ksharp · Accepted solution]

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

[Patrick]

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;

[Tom]

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;

[Haikuo]

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;