topic Re: How to go over a table multiple times but changing the set of observations each time? in New SAS User

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

eduqlm — Tue, 12 Mar 2019 18:19:42 GMT

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

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

PaigeMiller — Tue, 12 Mar 2019 18:33:52 GMT

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.

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

eduqlm — Tue, 12 Mar 2019 19:34:47 GMT

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?

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

PaigeMiller — Tue, 12 Mar 2019 19:52:14 GMT

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.

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

PGStats — Tue, 12 Mar 2019 21:12:04 GMT

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;

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

eduqlm — Wed, 13 Mar 2019 12:40:44 GMT

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!

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

eduqlm — Wed, 13 Mar 2019 12:42:35 GMT

Excellent!

I used this script and it worked seamlessly!

Thanks!!