Estimate standard deviation quarter by quarter

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Estimate standard deviation quarter by quarter

[ Edited ]

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

gvkeydatadateCompanyCOGSQSTD
100119830331A1.258NA
100119830630A1.4std from QI/1983 to QII/1983
100119830930A1.5std from QI/1983 to QIII/1983
100119831231A1.6 
……..………….……. 
100119860331A2.3 
100219840430B3.5 
100219840731B6.5 
…..………..….. 

 

 


Accepted Solutions
Solution
‎12-12-2017 07:24 AM
Valued Guide
Posts: 2,188

Re: Estimate standard deviation quarter by quarter

[ Edited ]

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 

View solution in original post


All Replies
PROC Star
Posts: 1,083

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112

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

Contributor
Posts: 37

Re: Estimate standard deviation quarter by quarter

[ Edited ]

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

PROC Star
Posts: 1,083

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112

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

 

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. 

 

 

 

 

PROC Star
Posts: 1,083

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112

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

Contributor
Posts: 37

Re: Estimate standard deviation quarter by quarter

Hi. I just post a sample of my data. Could you help me please?
PROC Star
Posts: 1,083

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112

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;
Contributor
Posts: 37

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112
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?

Valued Guide
Posts: 2,188

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112

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 

Valued Guide
Posts: 2,188

Re: Estimate standard deviation quarter by quarter

sorry I posted code from SAS Studio (so line-breaks are missing)
Shortly I'll post the code with line breaks
Solution
‎12-12-2017 07:24 AM
Valued Guide
Posts: 2,188

Re: Estimate standard deviation quarter by quarter

[ Edited ]

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 

Contributor
Posts: 37

Re: Estimate standard deviation quarter by quarter

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)?
Trusted Advisor
Posts: 1,149

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112

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.

Valued Guide
Posts: 2,188

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112
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.
Valued Guide
Posts: 2,188

Re: Estimate standard deviation quarter by quarter

Posted in reply to trungcva112
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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 197 views
  • 2 likes
  • 4 in conversation