## How can I perform calculations on specific rows?

Solved
Frequent Contributor
Posts: 75

# How can I perform calculations on specific rows?

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

Accepted Solutions
Solution
‎11-13-2012 03:26 PM
Super User
Posts: 6,781

## Re: How can I perform calculations on specific 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.

All Replies
Super User
Posts: 6,781

## Re: How can I perform calculations on specific rows?

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.

Frequent Contributor
Posts: 75

## Re: How can I perform calculations on specific rows?

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)

Super User
Posts: 23,754

## Re: How can I perform calculations on specific rows?

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

Solution
‎11-13-2012 03:26 PM
Super User
Posts: 6,781

## Re: How can I perform calculations on specific 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.

Frequent Contributor
Posts: 75

## Re: How can I perform calculations on specific rows?

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!

Super User
Posts: 10,784