turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How can I perform calculations on specific rows?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2012 02:05 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2012 03:26 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2012 02:24 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2012 03:05 PM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2012 03:23 PM

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

Solution

11-13-2012
03:26 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2012 03:26 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2012 03:39 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2012 11:50 PM

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