BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

hi,

suppose that I have the following table:

companydatedirector_idtenureceo_dummynon_ceo_dummy
A31/12/20101510
A31/12/20102701
A31/12/201035.501
A31/12/20104301

For each company-date combination I would like to get the number of non-ceo directors (denoted by the non_ceo_dummy = 1) whose tenure is greater than that of the ceo (denoted by ceo_dummy = 1).

So from the table above the ceo has a tenure of 5 years and directors 2 and 3 have tenures greater than that of the ceo (7 and 5.5 years respectively), so for that specific company-date combination there is a total of 2 directors with tenures greater than that of the ceo, and the new table should be like this:



companydatetotal_dir
A31/12/20102



Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
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

View solution in original post

18 REPLIES 18
ballardw
Super User

proc summary data=have nway;

     class company date;

     var non_ceo_dummy;

     output out=want(drop=_type_ _freq_) sum=total_dir;

run;

is one way

ilikesas
Barite | Level 11

Hi ballardw,

the code that you put is good for the overall sum, but in my case there is a condition which I apply and it is calculating the sum of the non ceo directors having a tenure greater than that of the ceo

thanks

slchen
Lapis Lazuli | Level 10

If I understand your question correctly, try this:

data want(keep=company date total_dir);

    set have;

  by company date  notsorted;

  retain term total_dir;

  if first.company or ceo_dummy=1 then do;

           term=tenure;

    total_dir=0;

  end;

    if tenure>term then total_dir+1;

    if last.company then output;

run;

ilikesas
Barite | Level 11

hi slchen,

I tries the code and it worked in the situation where the ceo is the first observation in the company-date combination, but if it is placed in another place then the code doesn't give the desired result. Is it possible to accommodate the code such that the place of the ceo is not important?

thank you

Astounding
PROC Star

Here's a variation that should work even when the CEO is in the middle of the data.  Assuming your data are already sorted, reorder on the fly:

data want;

  set have (where=(ceo_dummy=1))

       have (where=(ceo_dummy=0));

  by company date;

  if first.date then do;

     ceo_tenure = tenure;

     n_directors = 0;

  end;

  retain ceo_tenure;

  if ceo_dummy=0 and tenure > ceo_tenure then n_directors + 1;

  if last.date;

run;

It does assume that each company/date has data for a single CEO, otherwise the programming becomes more complicated.

Good luck.

P.S.  The SQL version looks fine too ... we were posting at the same time.  I'm just more comfortable with a DATA step.

ilikesas
Barite | Level 11

Hi Astounding,

the code is also intuitive, but I get 0 observations and no error message at all...

ilikesas
Barite | Level 11

I even tried to do the following:

data number;

input com cat tenure ceo_dummy ind_dummy;

datalines;

1 1 5 0 1

1 1 7 0 1

1 1 5.5 1 0

1 1 3 0 1

2 1 8 0 1

2 1 2 1 0

2 1 7 0 1

1 2 10 0 1

1 2 8 1 0

1 2 6 0 1

;

run;

proc sort data=number;

by com cat descending ceo_dummy;

run;

data number3(keep=com cat total_dir);

    set number;

  by com cat   ;

  retain term total_dir;

  if  ceo_dummy=1 then do;

           term=tenure;

    total_dir=0;

  end;

    if tenure>term then total_dir+1;

    if last.com then output;

  by com cat;

run;

but the result is:

1121
2212

so it seems to ignore the 1-1 combination...

stat_sas
Ammonite | Level 13

Try this.

proc sql;

select company, date, count(non_ceo_dummy) as total_dir from have

where ceo_dummy=0 and tenure>(select tenure from have where ceo_dummy=1)

group by company,date;

quit;

ilikesas
Barite | Level 11

Hi stat@sas,

the code is very intuitive but I get an error that the subquery evaluated to more than one row, so I guess that somewhere there might be a small syntax which halts the whole process...

stat_sas
Ammonite | Level 13

Based on the given data set for ceo_dummy=1 there is only observation and sub query will give only one row. Can you share log please?

ilikesas
Barite | Level 11

550  proc sql;

551  create table number5 as

552  select com, cat, count(ind_dummy) as total_dir from number

553  where ceo_dummy=0 and tenure>(select tenure from number where ceo_dummy=1)

554  group by com,cat;

ERROR: Subquery evaluated to more than one row.

stat_sas
Ammonite | Level 13

sql code is for the initial table.

ilikesas
Barite | Level 11

I think that I got something by doing the following:

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 sort data=number;

by com cat descending ceo_dummy;

run;

data number3(keep=com cat total_dir);

    set number;

  by com cat notsorted;

  retain term total_dir;

  if  ceo_dummy=1 then do;

           term=tenure;

    total_dir=0;

  end;

    if tenure>term then total_dir+1 ;

run;

proc sort data=number3;

by com cat descending total_dir;

run;

proc sql;

create table number4 as select com,cat,max(total_dir) as num_dir from number3

group by com,cat;

quit;

Although it is many steps...

ilikesas
Barite | Level 11

I redid the code exactly as is for the initial table but got the following result:

1121
2212

so its again the problem when the combination 1-1 is again being ignored

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