Hi All,
I have a data set of different stocks as follow:
permno year date rel day return (WANT)
a 1972 x-1 -1 x1 (1+x1)*(1+x2)+(1+x3)
a 1972 x 0 x2 (1+x1)*(1+x2)+(1+x3)
a 1972 x+1 1 x3 (1+x1)*(1+x2)+(1+x3)
a 1972 y-1 -1 y1 (1+ y1)+(1+y2)+(1+y3)
a 1972 y 0 y2 (1+ y1)+(1+y2)+(1+y3)
a 1972 y+1 1 y3 (1+ y1)+(1+y2)+(1+y3)
b 1972 z-1 -1 xx
b 1972 z 0 xx
b 1972 z+1 1 xx
b 1973 s-1 -1 xx
b 1973 s 0 xx
b 1973 s+1 1 xx
So, what I want to do is to calculate the sum of returns for each permno for each of the windows(from relday=-1 to relday=+1). (that is the WANT variable!)
For exmaple I want to calculate for a for 1972 for date x-1 to x+1 :
Can anyone help me with that? Thanks a lot in advance.
Shayan
Again, this solution is highly dependent on the values of relday. If you have the slightest doubt that they might not be in order, please mention it.
Replace "want + return;" with something more elaborate. For example:
if relday=-1 then want = return + 1;
else want = want * (return + 1);
That should do it as long as relday behaves. Actually, I'm not 100% certain because your formulas aren't consistent. Some use + and some use *. But this should be enough to give you the right idea on how to approach the problem.
If you can rely on the values of relday, this would do the trick:
data want;
want=0;
do until (relday=1);
set have;
want + return;
end;
do until (relday=1);
set have;
output;
end;
run;
Good luck.
Thanks a lot astounding.
but I have a problem. Actually, other than summing, I need to do some multiplications,too. So, for example, I need to calculate (1+x)(1+x2). I was trying to do it by summing over log s and then using exponential, but this will get me in trouble for negative returns. Could you please help me with that? ( I edited the main question to reflect that)
Why not transpose the data and operate on columns rather than rows?
Again, this solution is highly dependent on the values of relday. If you have the slightest doubt that they might not be in order, please mention it.
Replace "want + return;" with something more elaborate. For example:
if relday=-1 then want = return + 1;
else want = want * (return + 1);
That should do it as long as relday behaves. Actually, I'm not 100% certain because your formulas aren't consistent. Some use + and some use *. But this should be enough to give you the right idea on how to approach the problem.
Yea, I totally got the Idea. Thanks a lot, again!
By the way, sorry for the inconsistent formulas, now that I looked at it I found how messy it was!
It is easy if you use LAG() function to calculate the formula.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.