Calculating rolling RSI for each stock by using the proc expand function?

[gotchj1]

I am trying to calculate the technical indicator relative strength index for each stock (Permno); I found a code online that does this but also includes a macro. I saw a forum here that mentions using a proc expand function instead of the macro that loops from day 16 to day 7500 for each stock. However, I am unfamiliar with proc expand. I have provided the csv file i am working with as well as the code I am using. I will also provide the link to the website that shows the RSI calculation using the macro.

https://support.sas.com/resources/papers/proceedings12/163-2012.pdf

data RSI2;

set RSI;

by permno;

ladjprc = lag(QQQQ_Close);

if QQQQ_Close>ladjprc then gain1 = QQQQ_Close-ladjprc;

if QQQQ_Close<ladjprc then loss1 = ladjprc-QQQQ_Close;

if first.Permno then do;

gain1 = .;

loss1 = .;

end;

retain days 0;

day +1;

if first.permno then day = 0;

if gain1 = . then gain1 = 0;

if loss1 = . then loss1 = 0;

retain sumgain 0;

sumgain = sumgain + gain1;

retain sumloss 0;

sumloss = sumloss + loss1;

if day = 15 then do;

AveGain = (sumgain-gain1) / 14;

AveLoss = (sumloss-loss1) / 14;

RS = AveGain / AveLoss;

RSI = 100-100/(1+RS);

end;

run;

[Patrick]

From the docu here:

The EXPAND procedure converts time series from one sampling interval or frequency to another and interpolates missing values in time series.

Proc Expand helps you to prepare your time series data for analysis. The procedure supports many different algorithms for interpolation and you would need to tell us what you want to use.

The code logic required for a moving average gets of course simpler if the time series data doesn't contain missing data points. 

[gotchj1]

I will take a look at the docu for the expand function,thank you

[gotchj1]

The first RSI (on day 15) is calculated differnently than the RSI's that come afterward. But i need to use the previous Average Gain/Loss in each calculation when day is greater than 15. Could i add in an else statement that retains the previous Average gain or loss then recalculates the RSI. The new Average gain/loss for days greater than 15 is ((previous average gain/loss *13) + current gain/loss)/14

[Patrick]

@Kurt_Bremser 

If I recall right then it was you who provided some months ago as answer to another question around rolling "something" the generic data step logic how that's done. 
Unfortunately I can't find this answer anymore but I believe it would also clarify a few things for the OP here.

[gotchj1]

I was thinking that I don't need to do the proc expand function and was going to use the retain function. The average Gian and average loss start on day 14 and then are calculated differently on day 15 and later. I was trying this code but it doesn't seem to work properly. I just need to be able to start the retain from day 15.

data RSI2;
set RSI;
by permno days;
retain AveGain_new;
if days ge 16 then AveGain_new = AveGain;
AveGain_new=(AveGain_new *13 +gain)/14;
run;

[FloydNevseta · Accepted solution]

This should work for you. It calculates the mean gains and losses using a simple average for the first 14 daily changes, and Wilder's method for the remaining means. Hopefully, the comments in the code clearly explains the inner workings, but feel free to ask. 

data rsi;
set have;
by stock;

* compute daily change in price;
prev_close = lag(close);
change = close - prev_close;

* determine gain or loss;
if change > 0 then gain = change;
else if change < 0 then loss = abs(change);

* reset values when stock changes;
if first.stock then do;
    days = 0;
    prev_close = .;
    change = .;
    gain = .;
    loss = .;
    sum_gain = .;
    sum_loss = .;
    mean_gain = .;
    mean_loss = .;
end;

days + 1;

* save the calculated averages across iterations of data step;
retain mean_gain mean_loss;

* aggregate the daily gains and losses for the first 15 days (but only has 14 days of daily changes);
if days <= 15 then do;
    sum_gain + gain;
    sum_loss + loss;
    if days = 15 then do;
        * simple average of first 14 values for 1st average;
        mean_gain = sum_gain / 14;
        mean_loss = sum_loss / 14;
    end;
end;
else do;
    * Wilder's smoothing method for remaining averages > day 15;
    * the mean_ values on the right side of equation are prior means;
    mean_gain = (13 * mean_gain + coalesce(gain, 0)) / 14;
    mean_loss = (13 * mean_loss + coalesce(loss, 0)) / 14;
end;

* calculate RSI beginning at day 15 because
* 14 days of daily changes exist at this point;
if days >= 15 then do;
    rs = mean_gain / mean_loss;
    rsi = 100 - 100 / (1 + rs);
end;

run;

 I used the stock prices in sashelp.stocks to create the test data. Here is the code for that.

proc sort data=sashelp.stocks out=stocks;
where stock in ('IBM','Intel');
by stock date;
run;

* keep only 30 days of closing prices for each stock;
data have;
set stocks;
by stock;
retain days;
if first.stock then days = 0;
days + 1;
if days <= 30 then output;
keep stock date close;
run;