How to go over a table multiple times but changing the set of observations each time?

[eduqlm]

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!!!

[PaigeMiller]

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.

[eduqlm]

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?

[PaigeMiller]

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.

[eduqlm]

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!

[PGStats · Accepted solution]

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;

[eduqlm]

Excellent!

I used this script and it worked seamlessly!

Thanks!!