BoardID 324 | BoardName FIFTH THIRD BANCORP | Ticker FITB | ISIN US3167731005 | mktcap 16216 | annual report date Dec 2010 | #of directors 1.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2010 | 12.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2010 | 13.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2010 | 5.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2011 | 1.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2011 | 13.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2011 | 14.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2011 | 5.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2012 | 1.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2012 | 13.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2012 | 14.0000 |
324 | FIFTH THIRD BANCORP | FITB | US3167731005 | 16216 | Dec 2012 | 5.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Current | 1.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Current | 10.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Current | 11.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Current | 2.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Dec 1999 | 2.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Dec 1999 | 11.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Dec 1999 | 13.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Dec 1999 | 5.0000 |
428 | ABBOTT LABORATORIES | ABT | US0028241000 | 54433 | Dec 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?
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;
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 ;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.