12-08-2015 09:06 PM
Hi I have recently started to learn SAS and I am using it to caculate RSI for a company stock, on intraday minute data.
While my code works fine for a finite number of values, it runs infinitely if i increase the values in the loop.
It works fine when i put a value of "50" but not if i put "12579 " in the do loop in macro.
here is my code,
lastPrice = lag(Last);
if Last>lastPrice then do;
gain = Last-lastPrice;
if Last<lastPrice then do;
loss = lastPrice-Last;
retain counter 0;
if first._RIC then do;
if gain=. then gain=0;
if loss=. then loss=0;
retain sumGain 0;
sumGain = sumGain + gain;
retain sumLoss 0;
sumLoss = sumLoss + loss;
If counter=16 then do;
drop sumGain sumLoss;
%DO counter =17 %TO 12579;
lastAvgGain = Lag(AvgGain);
lastAvgLoss = Lag(AvgLoss);
If counter >16 then do;
AvgGain = (lastAvgGain*13+gain)/14;
AvgLoss = (lastAvgLoss*13+loss)/14;
12-08-2015 10:04 PM
12-08-2015 10:13 PM
thanks for replying.. it worked fine.
Also i need to figure out a way to run the do loop till the last observation in my dataset. right now i'm explicitly writing down the numerical value. i want to use somthing like this (%do i=15 %to(last observation)). thanks.
12-08-2015 10:30 PM
The DATA step automatically cycles through every observation. You don't need macros to make that happen. It does seem over the top that you have code that goes through the data over 12,000 times when it is likely that one time through would do the trick. If you explain what you would like as the outcome, you are pretty sure to get some good suggestions.
12-08-2015 10:56 PM
Yes, i did look at the log and it seems the data is being read again and again for 12579 times. How can i avoid that? I need to create a macro to calculate the RSI for 30 different stocks. Each stock will have different number of obs. I coded this to check if the macro works for one company. How can i avoid the data step being processed again and again.
I need the data step to be processed just once and then macro to run for each observation in data where counter is 17 and above till the last observation.
12-09-2015 09:47 AM - edited 12-09-2015 09:52 AM
I modified your macro to include a proc sql step before the data step that will count the number of observations in the data set specified in the macro call and insert that number into a macro called nobs. The do loop will then begin at 17 and run until it hits the number of observations in the given data set (for this instance the data set is apple). Also, I don't think you need the If then do statement on the &counter value because you are starting the counter at 17, it will always be greater than 16. You can probably remove that piece. You can just continue to call the macro and use a different data set name in as the parameter. Hope this helps!
%macro calculateRSI(data); proc sql noprint; select count(*) into: nobs from &data; quit; %do counter=17 %to &nobs; data &data; set &data; lastAvgGain = Lag(AvgGain); lastAvgLoss = Lag(AvgLoss); If &counter >16 then do; AvgGain = (lastAvgGain*13+gain)/14; AvgLoss = (lastAvgLoss*13+loss)/14; RS=AvgGain/AvgLoss; RSI=100-(100/(1+RS)); end; run; %end; %mend; %calculateRSI(apple);
12-09-2015 12:33 PM
There are a couple of confounding factors ... macro language loops, re-using the same data set, dividing by 14 but only starting the output calculations with 17. To get this into one step instead of 12,000 I suggest you list 20 lines of data. Show what you start with, what you want to end up with, and (if it's not 100% clear) how the calculations are made. It's still extremely likely that tne end result can be obtained with one pass through the data.