BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ksharp
Super User
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
Opal | Level 21

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
Super User Tom
Super User

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
Onyx | Level 15

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;

sas-innovate-2024.png

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.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 18 replies
  • 2311 views
  • 6 likes
  • 9 in conversation