topic Re: How can I perform calculations on specific rows? in SAS Procedures

How can I perform calculations on specific rows?

Shayan2012 — Tue, 13 Nov 2012 19:05:09 GMT

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

Re: How can I perform calculations on specific rows?

Astounding — Tue, 13 Nov 2012 19:24:57 GMT

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.

Re: How can I perform calculations on specific rows?

Shayan2012 — Tue, 13 Nov 2012 20:05:39 GMT

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)

Re: How can I perform calculations on specific rows?

Reeza — Tue, 13 Nov 2012 20:23:19 GMT

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

Re: How can I perform calculations on specific rows?

Astounding — Tue, 13 Nov 2012 20:26:25 GMT

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.

Re: How can I perform calculations on specific rows?

Shayan2012 — Tue, 13 Nov 2012 20:39:53 GMT

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!

Re: How can I perform calculations on specific rows?

Ksharp — Wed, 14 Nov 2012 04:50:40 GMT

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