Help using Base SAS procedures

1) how to keep the highest number of directors within the same annual report date of a company? 2) how to average the number of directors within the same annual report date of a company?

Reply
Frequent Contributor
Posts: 102

1) how to keep the highest number of directors within the same annual report date of a company? 2) how to average the number of directors within the same annual report date of a company?

BoardID

324

BoardName

FIFTH THIRD BANCORP

Ticker

FITB

ISIN

US3167731005

mktcap

16216

annual report date

Dec 2010

#of directors

1.0000

324FIFTH THIRD BANCORP FITB US316773100516216Dec 2010 12.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2010 13.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2010 5.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2011 1.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2011 13.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2011 14.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2011 5.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2012 1.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2012 13.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2012 14.0000
324FIFTH THIRD BANCORP FITB US316773100516216Dec 2012 5.0000
428ABBOTT LABORATORIES ABT US002824100054433Current 1.0000
428ABBOTT LABORATORIES ABT US002824100054433Current 10.0000
428ABBOTT LABORATORIES ABT US002824100054433Current 11.0000
428ABBOTT LABORATORIES ABT US002824100054433Current 2.0000
428ABBOTT LABORATORIES ABT US002824100054433Dec 1999 2.0000
428ABBOTT LABORATORIES ABT US002824100054433Dec 1999 11.0000
428ABBOTT LABORATORIES ABT US002824100054433Dec 1999 13.0000
428ABBOTT LABORATORIES ABT US002824100054433Dec 1999 5.0000
428ABBOTT LABORATORIES ABT US002824100054433Dec 2000 2.0000

For example, for the annualreportdate of “dec 2010” for boardID 324 (row 1-4) I see 1,12,13,5 as number of directors. I wanted to just keep the row with 13 (the greatest number). how can i do this and how do i do this for all the firms? or each report date?


Could you kindly provide your guidance?

1) how to keep the highest number of directors within the same annual report date of a company?

2) how to average the number of directors within the same annual report date of a company?

Super User
Posts: 19,814

Re: 1) how to keep the highest number of directors within the same annual report date of a company? 2) how to average the number of directors within the same annual report date of a company?

1)

proc sort data=have out=have;

by board_id annualReportDate descending numberDirectors;

data want;

set have;

by boarded annualreportdate;

if first.annualreportdate;

run;

2)

proc sort data=have out=have;

by board_id annualReportDate ;

var numberDirectors;

output out=want2 mean(numberDirectors)=avgnumdirectors;

run;

Valued Guide
Posts: 2,177

Re: 1) how to keep the highest number of directors within the same annual report date of a company? 2) how to average the number of directors within the same annual report date of a company?

This type of query is a natural fit for proc sql

Within the company/year group you derive the max #  and mean with code like

Proc sql ;

Create table summary_list as

select boardID, ticker, AnnualReportDate

     , max( numDir) as maxDir

     , mean( numDir) as avgNdir

from your.data

group by 1,2,3

;

quit ;

Trusted Advisor
Posts: 1,137

Re: 1) how to keep the highest number of directors within the same annual report date of a company? 2) how to average the number of directors within the same annual report date of a company?

The below code by proc means will accomplish both the requirements

proc means data=have mean max nway;

class BoardID boardName  Ticker ISIN mktcap date ;

var value;

output out=means max=max mean=mean;

run;

Thanks,

jag

Thanks,
Jag
Ask a Question
Discussion stats
  • 3 replies
  • 205 views
  • 0 likes
  • 4 in conversation