SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

counting the number of observations conditional on another observation

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

counting the number of observations conditional on another observation

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!


Accepted Solutions
Solution
‎05-16-2015 03:14 AM
Super User
Posts: 9,682

Re: counting the number of observations conditional on another observation

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


All Replies
Super User
Posts: 10,516

Re: counting the number of observations conditional on another observation

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

Super Contributor
Posts: 413

Re: counting the number of observations conditional on another observation

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

Super Contributor
Posts: 275

Re: counting the number of observations conditional on another observation

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;

Super Contributor
Posts: 413

Re: counting the number of observations conditional on another observation

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

Super User
Posts: 5,085

Re: counting the number of observations conditional on another observation

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.

Super Contributor
Posts: 413

Re: counting the number of observations conditional on another observation

Hi Astounding,

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

Super Contributor
Posts: 413

Re: counting the number of observations conditional on another observation

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...

Trusted Advisor
Posts: 1,204

Re: counting the number of observations conditional on another observation

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;

Super Contributor
Posts: 413

Re: counting the number of observations conditional on another observation

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...

Trusted Advisor
Posts: 1,204

Re: counting the number of observations conditional on another observation

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?

Super Contributor
Posts: 413

Re: counting the number of observations conditional on another observation

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.

Trusted Advisor
Posts: 1,204

Re: counting the number of observations conditional on another observation

sql code is for the initial table.

Super Contributor
Posts: 413

Re: counting the number of observations conditional on another observation

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...

Super Contributor
Posts: 413

Re: counting the number of observations conditional on another observation

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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