Help using Base SAS procedures

How can I perform calculations on specific rows?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

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: 5,518

Re: How can I perform calculations on specific rows?

Posted in reply to Shayan2012

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.

View solution in original post


All Replies
Super User
Posts: 5,518

Re: How can I perform calculations on specific rows?

Posted in reply to Shayan2012

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?

Posted in reply to Astounding

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: 19,878

Re: How can I perform calculations on specific rows?

Posted in reply to Shayan2012

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

Solution
‎11-13-2012 03:26 PM
Super User
Posts: 5,518

Re: How can I perform calculations on specific rows?

Posted in reply to Shayan2012

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?

Posted in reply to Astounding

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,048

Re: How can I perform calculations on specific rows?

Posted in reply to Shayan2012

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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