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

    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
Astounding
PROC Star

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

6 REPLIES 6
Astounding
PROC Star

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.

Shayan2012
Quartz | Level 8

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)

Reeza
Super User

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

Astounding
PROC Star

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.

Shayan2012
Quartz | Level 8

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!

Ksharp
Super User

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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