How can I perform calculations on specific rows?

[Shayan2012]

    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

[Astounding]

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.

[Shayan2012]

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)

[Reeza]

Why not transpose the data and operate on columns rather than rows?

[Astounding · Accepted solution]

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.

[Shayan2012]

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!

[Ksharp]

It is easy if you use LAG() function to calculate the formula.