topic How do I find the number of observations for each year with condition in SAS Procedures

How do I find the number of observations for each year with condition

fyuksel — Tue, 10 Oct 2017 03:35:36 GMT

I have a dataset like that. gvkey is firm identifier

gvkey	fyear	dvc	sharerepur	marketcap
1004	1990	7.651	2.326	224.4604
1004	1996	7.976	0	564.3237
1004	1998	9.375	7.558	540.7748
1004	1999	9.218	0	372.7519
1004	2012	0	14.6	790.0029
1004	2014	11.9	151.5	1046.395
1004	2015	10.4	18.8	842.5112
1019	1996	0	0.539	22.581
1019	1997	0.33	2.977	37.41
1019	1998	0.329	0.504	35.604
1019	2000	0.335	0	30.378
1019	2001	0.325	2.298	39.721
1034	1991	2.709	3.759	417.3085
1034	2006	7.384	307.397	1038.686

From this date table I want to find number of firms, dividend paying firms and repurchasing firms in each year as well as their percentage. The table will look like that:

year	number of firms	dividend payers	percentage of divident payer	repurchasers	percantege of repurchases
1990	1458	1052	72.15%	350	24.01%
1991	3452	852	24.68%	523	15.15%
1992	3452	965	27.95%	152	4.40%
1993	5499	123	2.24%	452	8.22%
1994	5212	185	3.55%	854	16.39%

I can get number of firms by proc freq but how can I get number of dividend payer and repurchaser?

Re: How do I find the number of observations for each year with condition

Reeza — Tue, 10 Oct 2017 03:39:44 GMT

@fyuksel wrote:

I can get number of firms by proc freq but how can I get number of dividend payer and repurchaser?

How are you calculating/defining that based on the data you've provided?

Re: How do I find the number of observations for each year with condition

s_lassen — Tue, 10 Oct 2017 09:33:57 GMT

@fyuksel:

If I understand your question correctly, what you want is something like this:

proc sql;
  create table want as select
    year,
    count(*) as no_of_firms,
    sum(DVC>0) as dividend_payers,
    calculated dividend_payers/calculated no_of_firms as pct_dividend_payers format=percent6.2,
    sum(sharerepur>0) as repurchasers,
    calculated repurchasers/calculated no_of_firms as pct_repurchasers format=percent6.2
  from have
  group by year
  order by year
  ;
quit;