Hi.
I have been searching for a solution to solve something I have to do, but I can't find the exact procedure.
Short: I need to calculate standar deviation in a given table.
Long: The table has for example 490 observations, something like this:
DATE USD PREV LN Id STD
---------- ----- ----- --------- --- ---------- 2019-01-04 37,36 37,45 -0,002414 1 2019-01-03 37,45 37,64 -0,004882 2 2019-01-02 37,64 37,67 -0,000805 3 2018-12-28 37,67 38,32 -0,017166 4 2018-12-27 38,32 38,56 -0,006324 5 ... 2017-12-26 18,33 18,07 0,014333 250 2017-12-22 18,07 17,94 0,007364 251 2017-12-21 17,94 17,76 0,010315 252 2017-12-20 17,76 17,62 0,007723 253 2017-12-19 17,62 17,57 0,002921 254 ... 2017-01-09 15,89 15,81 0,005206 486 2017-01-06 15,81 15,96 -0,009883 487 2017-01-05 15,96 16,08 -0,007364 488 2017-01-04 16,08 15,94 0,008900 489 2017-01-03 15,94 15,94 0,000031 490
USD: Dollar Exchange Rate
PREV: Dollar Exchange Rate from the previous day
LN: logarithm of (USD / PREV)
Id: As I'm working with only labour days I created this column to ease the look up for observations
STD: The variable I need to update
Now I need to do this:
- For DATE 2019-01-04 I need to calculate standard deviation for the observations (Id) 1 to 252 (that would be 2017-12-21) and update the value in "STD" variable (just for the record this number is 0,017168724)
-For the next DATE (2019-01-03) I need to calculate standard deviation but now for the observations 2 to 253 (that would be 2017-12-20) and update the value in "STD" variable (just for the record this number is 0,017167976)
And so on.
I managed to calculate it one by one preselecting the first 252 observations with something like this:
PROC SQL OUTOBS = 252;
CREATE TABLE WORK.DATA03 AS
SELECT *
FROM WORK.DATA02
WHERE DATE <= '04JAN2019'D;
QUIT;
And then:
PROC SQL;
CREATE TABLE WORK.DATA04 AS
SELECT MAX (DATE) FORMAT YYMMDD10. AS MaxDate,
STD (LN) AS STDDEV,
DATETIME () FORMAT = DATETIME20.2 AS ProcessDate
FROM WORK.DATA03;
QUIT;
But I would like a more elegant solution to go over the table and get it full updated.
I found some scripts using a loop but I need to loop multiple times, changing the set of observations each time.
I hope I made my self clear.
Thanks in advance!!!
An example calculating 12 month forward rolling STD using SASHELP data with proc expand:
proc sort data=sashelp.stocks out=stocks; by stock date; run;
proc expand data=stocks out=stocksSTD;
by stock;
id date;
convert Close=Close12Std / transformout=(reverse movstd 12 reverse );
run;
PROC EXPAND will do this. If you don't have access to PROC EXPAND (because its not part of your SAS license), there are other ways, but I'm not familiar with them, however a search of this forum ought to find the proper methods.
Hi.
Thanks for the response.
I been reading for it in the documentation, but it's not exactly what I need.
I need to select observations 1 to 252 and update column STD in observation 1.
Then select observations 2 to 253 and update column STD in observation 2.
And so on.
May be I'm missing something in PROC EXPAND?
EXPAND will compute the moving standard deviations of each group of 252 observations and store them in a new data set. Then you can merge the results in the new data set into the original data set any way you need them.
Perfect! It was giving me an error because I writing "STD" instead of "MOVSTD"
Wrong: TRANSFORMOUT = (REVERSE STD 252 REVERSE)
Correct: TRANSFORMOUT = (REVERSE MOVSTD 252 REVERSE)
Thanks!
An example calculating 12 month forward rolling STD using SASHELP data with proc expand:
proc sort data=sashelp.stocks out=stocks; by stock date; run;
proc expand data=stocks out=stocksSTD;
by stock;
id date;
convert Close=Close12Std / transformout=(reverse movstd 12 reverse );
run;
Excellent!
I used this script and it worked seamlessly!
Thanks!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.