BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

## 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.

6 REPLIES 6
PROC Star

## 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.

Quartz | Level 8

## 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

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

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

PROC Star

## 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.

Quartz | Level 8

## 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

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

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

Discussion stats
• 6 replies
• 1149 views
• 7 likes
• 4 in conversation