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

Hi everyone, 

 

I'm trying to get weekly returns (Monday to Friday) from my daily returns data set, for each week and each company over my sample period. Can anyone help me with that? (see example below of my data set with two companies, just for one week). Hope that's enough, let me know if you need more info. Thanks! 

 

PERMNO                    DATE                     RET

10158                     28/04/2014               -0.01

10158                     29/04/2014               -0.006 

10158                     30/04/2014                0.006

10158                     01/05/2014               -0.009          

10158                     02/05/2014                0.002  

10234                     28/04/2014                -0.02

10234                     29/04/2014                -0.005 

10234                     30/04/2014                0.001

10234                     01/05/2014               -0.007          

10234                     02/05/2014                0.003  

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

Just a small modification.

data WANT;
  set HAVE;
  by PERMNO notsorted; 
  retain CUMUL;
  if first.PERMNO or weekday(DATE)=2 then CUMUL=1+RET;
  else CUMUL=CUMUL*(1+RET);
  if last.PERMNO or weekday(DATE)=6 then do;
    CUMUL=CUMUL-1; 
    output;
  end;  
  drop RET;
run;
 
PERMNO DATE CUMUL
10158 19845 -0.016983
10158 19852 -0.041954
10234 19845 -0.027850

 

 

View solution in original post

12 REPLIES 12
Reeza
Super User
Rolling weekly or just weekly? Assuming you're doing multiplicative accumulation?
Can you show what you want as your output?

Antoine44
Fluorite | Level 6
Just weekly 🙂
So for 10158 I should get -0.01698. That's (-0.01+1)*(-0.006+1)*(0.006+1)*(-0.009+1)*(0.002+1) - 1
For 10234 I should get -0.02785. That's (-0.02+)1*(-0.005+1)*(0.001+1)*(-0.007+1)*(0.003+1) - 1
ChrisNZ
Tourmaline | Level 20

Like this (Assuming all dates must be used since that's the data provided)?

data WANT;
  set HAVE;
  by PERMNO notsorted;
  retain CUMUL;
  if first.PERMNO then CUMUL=1+RET;
  else CUMUL=CUMUL*(1+RET);
  if last.PERMNO then  do;
    CUMUL=CUMUL-1; 
    output;
  end;  
  drop DATE RET;
run; 

 
 
PERMNO CUMUL
10158 -0.016983
10234 -0.027850

 

 

Antoine44
Fluorite | Level 6
Sorry, that works for my example but I just realised it won't work (I think) for what I actually have. Like this:

PERMNO DATE RET Weekly Ret

10158 28/04/2014 -0.01
10158 29/04/2014 -0.006
10158 30/04/2014 0.006
10158 01/05/2014 -0.009
10158 02/05/2014 0.002 -0.16983
10158 05/05/2014 -0.04
10158 06/05/2014 -0.006
10158 07/05/2014 0.002
10158 08/05/2014 -0.003
10158 09/05/2014 0.005 -0.04195
10234 28/04/2014 -0.02
10234 29/04/2014 -0.005
10234 30/04/2014 0.001
10234 01/05/2014 -0.007
10234 02/05/2014 0.003 -0.027850

So I've added an extra week for 10158 cause for each company I've got multiple consecutive weeks. Hope that makes sense?
Reeza
Super User

Do you want calendar weeks or just 7 days? Does what is a week for one PermNo be the same week for another PERMNO or can they use different days because say they start on different dates? I think you'll need to expand your example data. 

 

 

Antoine44
Fluorite | Level 6
Maybe I can share my data set to show you what I mean. Can I share my dataset or maybe screenshot would do?
Antoine44
Fluorite | Level 6

I have attached a text file with one company only. Each week is assigned a number and I just need the weekly returns for each one of them. I have other companies too with returns in the same weeks as the one attached so it needs by week and by permno and by year as well cause you'll find the same number assigned to a  week every year. Does this help?

 

 

ChrisNZ
Tourmaline | Level 20

Just a small modification.

data WANT;
  set HAVE;
  by PERMNO notsorted; 
  retain CUMUL;
  if first.PERMNO or weekday(DATE)=2 then CUMUL=1+RET;
  else CUMUL=CUMUL*(1+RET);
  if last.PERMNO or weekday(DATE)=6 then do;
    CUMUL=CUMUL-1; 
    output;
  end;  
  drop RET;
run;
 
PERMNO DATE CUMUL
10158 19845 -0.016983
10158 19852 -0.041954
10234 19845 -0.027850

 

 

Antoine44
Fluorite | Level 6
Hi Chris,

Thanks for that, that works most of the time. However, I just noticed that it doesn't work when the week starts on weekday 3 (because day 2 would've been a public holiday).
Any chance you know how to get around that?
Thanks for your help!

Antoine44
Fluorite | Level 6
Never mind I think I got it working :slightly_smiling_face:

Thanks again for your help!
Shmuel
Garnet | Level 18

Assuming data is sorted by company and date try next code:

data temp; 
 set have;
    by premno date;
        retain week_count 0  week_total 0;
        if first.premno then do;  week_count = 0; week_total = 0; end;

        if weekday(date) = 1  /* assuming monday */
            then  do; week_count + 1; week_total = 0; end;
       week_total = week_total + ret; 
run;
data want;
  set temp;
   by premno week_count;
      if   last.wee_count;
run;
Antoine44
Fluorite | Level 6
Alright thanks, I'll try that

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 12 replies
  • 2584 views
  • 0 likes
  • 4 in conversation