How to create new table with statistics for every cross-section using panel data

[dimuly0]

Dear community,

I am a fairly unexperienced SAS user and want to know the following since I cannot figure it out on myself.

Assume I have a huge dataset containing time series of multiple companies' earnings and cash flows.

The number of companies is around 20000 and there is data for 3-10 consecutive years for every company. 

Now I need to calculate the "Smoothness of earnings" measured by std(earnings) / std(cash flows) preferably for every company so that I can extract percentiles and the mean of all companies' smoothness values.

Is there a way to create a new table containing above mentioned information?

Perhaps some sort of listed standard deviations for earnings and cash flows regarding every company ID?

I assume there might be a way using proc sql, but I don't know the exact code to use, maybe you do 🙂

Any help will be highly appreciated, thanks in advance!

[PaigeMiller]

Show us a small portion of this table of data.

[dimuly0]

Sure, here you go. I hope you can see the pdf

gvkey = indicates the individual company ID

ib = earnings before extraordinary items

oancf = operating cash flow

[dimuly0]

Sure, here you go.

gvkey = Unique Company ID

ib = earnings before extraordinary items

oancf = cash flow

Beob.      gvkey fyear          ib              oancf

001003	1987	-0.5250	-0.9870	-0.5250
001003	1988	-7.8380	-2.1000	-7.8380
001003	1989	-2.5540	-0.0320	-2.5540
001004	2013	72.9000	139.8000	19.7000
001004	2014	-54.5000	-43.0000	-67.4000
001004	2015	40.5000	32.1000	45.5000
001004	2016	50.2000	21.8000	52.0000
001004	2017	73.7000	64.3000	73.7000
001009	1988	1.2660	1.6540	1.2660
001009	1989	1.3170	2.2260	1.3170
001009	1990	1.4310	2.0750	1.4310
001009	1991	0.8570	3.3790	0.8570
001009	1992	2.3880	6.0240	2.3880
001009	1993	3.3620	3.4470	3.3620
001009	1994	4.5230	1.9250	4.5230
001010	2001	145.7000	114.2000	72.0000
001010	2002	79.7000	165.3000	77.3000
001011	1991	-1.4480	-0.2430	-1.8110
001011	1992	-2.1530	-0.9410	-2.1560
001011	1993	-3.1580	-0.8170	-3.1580
001011	1994	-9.1980	2.7190	-9.1980
001012	1988	-0.1810	1.8660	-0.1810
001012	1989	0.9170	2.7350	0.9170
001013	2004	31.3000	3.1000	29.0000
001013	2005	85.5000	58.6000	96.0000
001013	2006	94.2000	87.1000	93.3000
001013	2007	113.3000	141.8000	123.5000
001013	2008	-44.4000	175.6000	-39.4000

[PaigeMiller · Accepted solution]

That's helpful.

Something like this will calculate the standard deviations you want.

proc summary data=have nway; 
   class gvkey;
   var ib oancf;
   output out=_stats_ stddev=ib_s oancf_s;
run;

Then you can do the division in a subsequent data step.

data want;
    set _stats_;
    smoothness = ib_s/oancf_s;
run;

[dimuly0]

The code worked perfectly.

Thank you very much!