rolling standard deviation - quarterly panel data

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

rolling standard deviation - quarterly panel data

I am working on creating a rolling standard deviation for my quarterly change in proft rate.  I want my standard deviation in time t to be the standard deviation of my chgprofrate for 4 quarters or time t-3 to t.

 

My data looks like:

 

Cusip    Quarter    ChgProfRate    

1            2003.1     -.12

1            2003.2     -.035

1            2003.3     .04

1            2003.4     .023

1            2004.1     .011

1            2004.2     -.013

2            2003.1     .054

2            2003.2     .012

2            2003.3     .022

2            2003.4     -.01

2            2004.1     .101

2            2004.2     .035

 

I used the forum from a previous question to get my code this far.  I ran the code below, but I am having trouble with the format of quarter.  I get the error " ERROR: Expression using subtraction (-) requires numeric types.
ERROR: Expression using BETWEEN has components that are of different data types. "

 

I am using the code:

 

proc sql;
create table want as
select *,(select std(chgprofrate) from have where quarter between a.quarter-3 and a.quarter and cusip=a.cusip) as rolling_std
from have as a;
quit;

 

THANK YOU FOR YOUR HELP!


Accepted Solutions
Solution
‎07-07-2016 07:38 AM
Grand Advisor
Posts: 9,596

Re: rolling standard deviation - quarterly panel data

ERROR said  You don't have the right type for variable Quarter.

 

data have;
input Cusip    _Quarter  $  ChgProfRate ;
Quarter=input(translate(_Quarter,'Q','.'),yyq6.);
format Quarter   yyqp.;
drop _Quarter ;
cards;  
1            2003.1     -.12
1            2003.2     -.035
1            2003.3     .04
1            2003.4     .023
1            2004.1     .011
1            2004.2     -.013
2            2003.1     .054
2            2003.2     .012
2            2003.3     .022
2            2003.4     -.01
2            2004.1     .101
2            2004.2     .035
;
run;

proc sql;
create table want as
select *,(select std(chgprofrate) from have where quarter between intnx('qtr',a.quarter,-3) and a.quarter and cusip=a.cusip) as rolling_std
from have as a;
quit;

View solution in original post


All Replies
Solution
‎07-07-2016 07:38 AM
Grand Advisor
Posts: 9,596

Re: rolling standard deviation - quarterly panel data

ERROR said  You don't have the right type for variable Quarter.

 

data have;
input Cusip    _Quarter  $  ChgProfRate ;
Quarter=input(translate(_Quarter,'Q','.'),yyq6.);
format Quarter   yyqp.;
drop _Quarter ;
cards;  
1            2003.1     -.12
1            2003.2     -.035
1            2003.3     .04
1            2003.4     .023
1            2004.1     .011
1            2004.2     -.013
2            2003.1     .054
2            2003.2     .012
2            2003.3     .022
2            2003.4     -.01
2            2004.1     .101
2            2004.2     .035
;
run;

proc sql;
create table want as
select *,(select std(chgprofrate) from have where quarter between intnx('qtr',a.quarter,-3) and a.quarter and cusip=a.cusip) as rolling_std
from have as a;
quit;
Contributor
Posts: 23

Re: rolling standard deviation - quarterly panel data

thanks a ton!
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 253 views
  • 0 likes
  • 2 in conversation