topic Re: counting the number of observations conditional on another observation in SAS Data Management

counting the number of observations conditional on another observation

ilikesas — Fri, 15 May 2015 22:16:56 GMT

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!

Re: counting the number of observations conditional on another observation

ballardw — Fri, 15 May 2015 22:35:11 GMT

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

Re: counting the number of observations conditional on another observation

slchen — Fri, 15 May 2015 23:15:35 GMT

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;

Re: counting the number of observations conditional on another observation

ilikesas — Sat, 16 May 2015 01:38:41 GMT

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

Re: counting the number of observations conditional on another observation

ilikesas — Sat, 16 May 2015 01:47:14 GMT

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

Re: counting the number of observations conditional on another observation

ilikesas — Sat, 16 May 2015 02:19:16 GMT

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

Re: counting the number of observations conditional on another observation

stat_sas — Sat, 16 May 2015 02:52:11 GMT

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;

Re: counting the number of observations conditional on another observation

Astounding — Sat, 16 May 2015 02:57:46 GMT

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.

Re: counting the number of observations conditional on another observation

ilikesas — Sat, 16 May 2015 03:02:40 GMT

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

Re: counting the number of observations conditional on another observation

ilikesas — Sat, 16 May 2015 03:06:44 GMT

Hi Astounding,

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

Re: counting the number of observations conditional on another observation

stat_sas — Sat, 16 May 2015 03:13:32 GMT

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?

Re: counting the number of observations conditional on another observation

ilikesas — Sat, 16 May 2015 03:15:25 GMT

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.

Re: counting the number of observations conditional on another observation

stat_sas — Sat, 16 May 2015 03:20:58 GMT

sql code is for the initial table.

Re: counting the number of observations conditional on another observation

ilikesas — Sat, 16 May 2015 03:27:09 GMT

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

Re: counting the number of observations conditional on another observation

ilikesas — Sat, 16 May 2015 03:30:41 GMT

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

Re: counting the number of observations conditional on another observation

Ksharp — Sat, 16 May 2015 07:14:18 GMT

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

Re: counting the number of observations conditional on another observation

Patrick — Sat, 16 May 2015 12:35:15 GMT

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)

)

as total_dir

from

have o

where ceo_dummy='1'

;

quit;

Re: counting the number of observations conditional on another observation

Tom — Sat, 16 May 2015 14:09:28 GMT

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;

Re: counting the number of observations conditional on another observation

Haikuo — Sat, 16 May 2015 17:42:41 GMT

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;