topic Re: Estimate standard deviation quarter by quarter in SAS Programming

Estimate standard deviation quarter by quarter

trungcva112 — Mon, 11 Dec 2017 13:24:11 GMT

Hi.

I have a (company ID-time) panel data. Now I have to calculate standard deviation of variable COGSQ from quarter I/1983 to quarter II/1983, and then quarter I/1983 to quarter III/1983, and so on.....

So basically, for company with GVKEY 001001;

t=0 is quarter I/1983

at t=1 (QII/1983), I need to find standard deviation of X from t=0 to t=1

at t=2 (QIII/1983), std of X from t=0 to t=2

and so on,

Do you have any idea how to do this?

So the dataset is like this

gvkey	datadate	Company	COGSQ	STD
1001	19830331	A	1.258	NA
1001	19830630	A	1.4	std from QI/1983 to QII/1983
1001	19830930	A	1.5	std from QI/1983 to QIII/1983
1001	19831231	A	1.6
……..	……	…….	…….
1001	19860331	A	2.3
1002	19840430	B	3.5
1002	19840731	B	6.5
…..	……	…..	…..

Re: Estimate standard deviation quarter by quarter

PeterClemmensen — Mon, 11 Dec 2017 11:26:43 GMT

Do you have SAS/ETS? If so check out PROC EXPAND 🙂

Re: Estimate standard deviation quarter by quarter

trungcva112 — Mon, 11 Dec 2017 13:01:39 GMT

Hi, I just check the PROC EXPAND but it is for transform/interpolate variables, so I dont see how this related to my case. Could you explain more for me? Please, I'm a newbie

Please note that this is different from rolling window standard deviation. Rolling window std has a fixed number of observations (fixed window range) but in this situation, the window range is larger when time progress

Re: Estimate standard deviation quarter by quarter

PeterClemmensen — Mon, 11 Dec 2017 13:02:02 GMT

PROC EXPAND is indeed for transforming/interpolating variables and you want to transform a variable 🙂

Take a look at the Transformation Operators site at the documentation. You want to create a moving stadard deviation, so the MOVSTD operator might be worth taking a look at.

Re: Estimate standard deviation quarter by quarter

PeterClemmensen — Mon, 11 Dec 2017 13:02:47 GMT

Post some sample of your data (not in the form of a picture) if you want some code answer 🙂

Re: Estimate standard deviation quarter by quarter

trungcva112 — Mon, 11 Dec 2017 13:24:52 GMT

Hi. I just post a sample of my data. Could you help me please?

Re: Estimate standard deviation quarter by quarter

PeterClemmensen — Mon, 11 Dec 2017 14:07:16 GMT

Ah, I understand. You can still use PROC EXPAND, but this may be simpler to do in a data step like this:

data have; 
input gvkey$ datadate:yymmdd10. Company$ COGSQ;
quarter=qtr(datadate);
year=year(datadate);
format datadate yymmdd10.;
datalines;
1001 19830331 A 1.258
1001 19830630 A 1.4 
1001 19830930 A 1.5
1001 19831231 A 1.6
1001 19860331 A 2.3
1002 19840430 B 3.5
1002 19840731 B 6.5
;

proc sort data=have;
	by gvkey year quarter;
run;

data want;
	set have;
	by gvkey;
	if first.gvkey then COGSQ_first=COGSQ;

	if not first.gvkey then std_dev = std(COGSQ_first, COGSQ);
	
	retain COGSQ_first;
	drop COGSQ_first;
run;

Re: Estimate standard deviation quarter by quarter

trungcva112 — Mon, 11 Dec 2017 14:37:11 GMT

Thank you, but I am confused a little bit about the line:
if not first.gvkey then std_dev = std(COGSQ_first, COGSQ);

So assume that it is on quarter III/1983, so this line will give me std_dev of COGSQ for 3 observations: Q1, Q2, Q3/1983 or just std_dev of 2 observations:Q1 and Q3/1983?

Re: Estimate standard deviation quarter by quarter

Peter_C — Mon, 11 Dec 2017 15:52:26 GMT

if this is only qtr/monthly intervals, it might be a good case to demonstrate array handling. SAS/IML would also simplify the resultant solution. What follows uses only a data step and assumes the monthly data arrive in month order within each company

data progressive_stddevs ;
do item= 1 by 1 until( last.company ) ;
 set your_data ;
 by company ;
 array cogX(1200) ; * 1200 months, probably enough per company? ;
 cogX(item) = cogsq ;
 n_cogs = n( of cogX(*) ) ;
 if n_cogs > 1 then COGSTD = std( of cogX(*) ) ;
 output ;
end ;
drop cogX: ;
run ;

the array is filled progressively until end of the company, when it will be emptied by restarting the data step iteration

The STD() function ignores empty cells in the array

Re: Estimate standard deviation quarter by quarter

Peter_C — Mon, 11 Dec 2017 15:53:27 GMT

sorry I posted code from SAS Studio (so line-breaks are missing)
Shortly I'll post the code with line breaks

Re: Estimate standard deviation quarter by quarter

Peter_C — Mon, 11 Dec 2017 16:03:42 GMT

my code with line breaks to make it easier to read

data progressive_stddevs ;
do item= 1 by 1 until( last.company ) ;
   set your_data ;
   by company ;
   array cogX(1200) ; * 1200 months, probably enough per company? ;
   cogX(item) = cogsq ;
   n_cogs = n( of cogX(*) ) ;
   if n_cogs > 1 then COGSTD = std( of cogX(*) ) ;
   output ;
end ;
drop cogX: ; 
run ;

the array is filled progressively until end of the company, when it will be emptied by restarting the data step iteration

The STD() function ignores empty cells in the array

Re: Estimate standard deviation quarter by quarter

trungcva112 — Tue, 12 Dec 2017 02:25:00 GMT

Thanks. I will try that. But each company has different number of observations. For example, company 1 has data from Q1/1983 to Q1/1986 (13 observations), company 2 has data Q1/1984 to QI/2000 (64 observations). So can I still use array cogX(1200)?

Re: Estimate standard deviation quarter by quarter

mkeintz — Tue, 12 Dec 2017 03:53:13 GMT

You want the std of within-company quarterly changes in cost-of-goods-sold (COGSQ), in which each quarter may have a slightly different population of companies (with id variable GVKEY).

Your data are sorted by GVKEY/DATADATE. So if you calculate every quarterly change (use the DIF function, where DIF(x)=X-lag(X)), then just keep every record but the first record for each GVKEY. Also make sure datadate and lag(datadate) are exactly 3 months apart (to avoid missed quarters or misalignment due to companies changing fiscal year definitions).

Once you've made this data set (call it NEED), then run proc means on it, with class datadate. Using the ODS output statement, and the STACKODSOUTPUT option puts data set file WANT in a nice format:

data need /view=need;
  set have;
  by gvkey datadate;
  cogsq_change=dif(cogsq);
  months_elapsed = intck('month',lag(datadate),datadate);

  if first.gvkey=0 and months_elapsed=3;
run;

ods output summary=want;
ods listing close;
proc means data=need stackodsoutput;
  class datadate;
  var cogsq_change;
run;
ods output close;
ods listing;

This will produce 5 variables for each datadate, one each for N, MEAN, STD, MIN, and MAX value of cogsq_change. The datadate identified the last date of the quarterly change. If you want more statistics, then make a list of wanted stats on the proc means statement.

The ODS LISTING CLOSE, and ODS LISTING avoids the (presumably unwanted) printing of all those stats. Instead the ods output summary=want; statement tells sas to put those results into a data set file named want.

Re: Estimate standard deviation quarter by quarter

Peter_C — Tue, 12 Dec 2017 10:06:44 GMT

Reply to @trungcva112
The array can be a fixed size. That needs to be large enough for the company with the most history. I thought 1200, to allow for 100 years of monthly data. If there is less history it is not a problem.
Like all SAS statistical functions only non-missing values will be used.
At the start of each data step iteration the whole array is set missing. Each value is inserted into the array just after each set statement and a STD() calculated. Each data step iteration ends when all rows of a company have been read.

Re: Estimate standard deviation quarter by quarter

Peter_C — Tue, 12 Dec 2017 10:09:36 GMT

There might be a problem if a company has tlmore than one period of history. That data step would treat all data for a company as one history

Re: Estimate standard deviation quarter by quarter

Peter_C — Tue, 12 Dec 2017 10:12:01 GMT

The DO loop could be extended to collect the min and max asset value or return, during the 'history to date'

Re: Estimate standard deviation quarter by quarter

trungcva112 — Tue, 12 Dec 2017 12:34:00 GMT

@Peter_C
Your code works. Thank you very much. You are a life saver.
@mkeintz
Thank you. Your code gives me a STD of all firms' COGSQ in a given datadate (quarter). But I need a progressive STD of COGSQ of each firms when time passes.
But your code will surely be a useful reference for me in the furture.
@All

Thank you everyone