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;
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;
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.
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.
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;
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.