counting the number of observations conditional on another observation

[ilikesas]

hi,

suppose that I have the following table:

company	date	director_id	tenure	ceo_dummy	non_ceo_dummy
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

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:

company	date	total_dir
A	31/12/2010	2

Thank you!

[ballardw]

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]

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]

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]

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]

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]

Hi Astounding,

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

[ilikesas]

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:

1	1	2	1
2	2	1	2

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

[stat_sas]

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]

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]

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]

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]

sql code is for the initial table.

[ilikesas]

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]

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

1	1	2	1
2	2	1	2

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