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

How can I convert daily returns  to monthly cumulative returns with proc expand convert?

Please note that some of the daily returns might be zero. Part of the data is attached.

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

I get a different cumulative sum then you do for March, but is the following what you are trying to get?:

data need;
  set have;
  yymm=100*year(date)+month(date);
run;

data want;
  set need;
  by entity yymm;
  retain cumret;
  amt=1+ret;
  if first.yymm then cumret=amt;
  else cumret=cumret*amt;
  if last.yymm then do;
    cumret=cumret-1;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post

12 REPLIES 12
Reeza
Super User


How can I convert daily returns  to monthly cumulative returns with proc expand convert?

 

How are you defining monthly cumulative returns?

Something like the following may be what you're looking for. If you have 0's that should be fine mathematically but if you have missing dates that may cause issues. If the 0's don't make sense from a business perspective that's a different issue. 

 

 

proc expand data=sample out=want from=day to=month;
by entity;
id date;
convert return / observed=total;
run;

 


@vickyCh wrote:

How can I convert daily returns  to monthly cumulative returns with proc expand convert?

Please note that some of the daily returns might be zero. Part of the data is attached.

 



 

vickyCh
Obsidian | Level 7

cumulative return=(1+r1)(1+r2)…(1+rn)-1

 

What I tried to do was the following code:

 

proc import

datafile="C:\temp\sample.xlsx"

dbms=xlsx

out=work.sample

replace;

run;

 

proc expand data=sample out=want method=none from=day to=month;
by entity;
id date;
convert ret = cum_return / transformin=(+1) transformout=(movprod n-1);

run;

 

However the number of periods, n, for each month might be different.

That was the point confused me.

Reeza
Super User

Why not CUPROD?

 

If that doesn't work, perhaps a data step is easier. 

Can you include some expected data so that we can ensure the output is what you need?

vickyCh
Obsidian | Level 7

For example, the cumulative returns for 2000/1/31,2000/2/29 and 2000/3/31 are  0.108340502,  -0.04989111 and   0.091433277 respectively for entity 1079.

DATE                Ret      entity       cumulative return
2000/1/3 0.026655203 1079
2000/1/4 -0.040201005 1079
2000/1/5 0 1079
2000/1/6 -0.0008726 1079
2000/1/7 0.045414846 1079
2000/1/10 0.009189641 1079
2000/1/11 -0.033940397 1079
2000/1/12 0.047129393 1079
2000/1/13 0 1079
2000/1/14 0.07692308 1079
2000/1/18 0.009118541 1079
2000/1/19 -0.018072288 1079
2000/1/20 0.009202454 1079
2000/1/21 -0.003799392 1079
2000/1/24 -0.025934402 1079
2000/1/25 -0.02349256 1079
2000/1/26 0.008019246 1079
2000/1/27 0.0548926 1079
2000/1/28 -0.046757165 1079
2000/1/31 0.019778481 1079      0.108340502
2000/2/1 0.058184639 1079
2000/2/2 -0.045454547 1079
2000/2/3 0.006144393 1079
2000/2/4 0.00610687 1079
2000/2/7 -0.023520486 1079
2000/2/8 -0.012432013 1079
2000/2/9 0.002360346 1079
2000/2/10 -0.028436018 1079
2000/2/11 -0.038211383 1079
2000/2/14 0.014370245 1079
2000/2/15 -0.0125 1079
2000/2/16 0.009282701 1079
2000/2/17 -0.008361204 1079
2000/2/18 -0.005059022 1079
2000/2/22 -0.001694915 1079
2000/2/23 -0.008488964 1079
2000/2/24 0.030821918 1079
2000/2/25 0.024086379 1079
2000/2/28 0.0081103 1079
2000/2/29 -0.020917136 1079       -0.04989111
2000/3/1 -0.023829088 1079
2000/3/2 -0.004208754 1079
2000/3/3 0.026204566 1079
2000/3/6 0.005766063 1079
2000/3/7 -0.022932023 1079
2000/3/8 0.015926236 1079
2000/3/9 0.023927392 1079
2000/3/10 0.003223207 1079
2000/3/13 0 1079
2000/3/14 -0.022489959 1079
2000/3/15 0.036154479 1079
2000/3/16 0.062648691 1079
2000/3/17 -0.043283582 1079
2000/3/20 0.010920437 1079
2000/3/21 -0.010030864 1079
2000/3/22 0.01948558 1079
2000/3/23 0.064220183 1079
2000/3/24 -0.01867816 1079
2000/3/27 -0.004392386 1079
2000/3/28 -0.0125 1079
2000/3/29 -0.034996279 1079
2000/3/30 0.010802469 1079
2000/3/31 0.011450382 1079    0.091433277

Ksharp
Super User

It is very easy for IML.

 

data have;
input DATE : yymmdd10.           Ret      entity ;
yymm=100*year(date)+month(date);
format date yymmdd10.;
cards;
2000/1/3 0.026655203 1079
2000/1/4 -0.040201005 1079
2000/1/5 0 1079
2000/1/6 -0.0008726 1079
2000/1/7 0.045414846 1079
2000/1/10 0.009189641 1079
2000/1/11 -0.033940397 1079
2000/1/12 0.047129393 1079
2000/1/13 0 1079
2000/1/14 0.07692308 1079
2000/1/18 0.009118541 1079
2000/1/19 -0.018072288 1079
2000/1/20 0.009202454 1079
2000/1/21 -0.003799392 1079
2000/1/24 -0.025934402 1079
2000/1/25 -0.02349256 1079
2000/1/26 0.008019246 1079
2000/1/27 0.0548926 1079
2000/1/28 -0.046757165 1079
2000/1/31 0.019778481 1079     
2000/2/1 0.058184639 1079
2000/2/2 -0.045454547 1079
2000/2/3 0.006144393 1079
2000/2/4 0.00610687 1079
2000/2/7 -0.023520486 1079
2000/2/8 -0.012432013 1079
2000/2/9 0.002360346 1079
2000/2/10 -0.028436018 1079
2000/2/11 -0.038211383 1079
2000/2/14 0.014370245 1079
2000/2/15 -0.0125 1079
2000/2/16 0.009282701 1079
2000/2/17 -0.008361204 1079
2000/2/18 -0.005059022 1079
2000/2/22 -0.001694915 1079
2000/2/23 -0.008488964 1079
2000/2/24 0.030821918 1079
2000/2/25 0.024086379 1079
2000/2/28 0.0081103 1079
2000/2/29 -0.020917136 1079    
2000/3/1 -0.023829088 1079
2000/3/2 -0.004208754 1079
2000/3/3 0.026204566 1079
2000/3/6 0.005766063 1079
2000/3/7 -0.022932023 1079
2000/3/8 0.015926236 1079
2000/3/9 0.023927392 1079
2000/3/10 0.003223207 1079
2000/3/13 0 1079
2000/3/14 -0.022489959 1079
2000/3/15 0.036154479 1079
2000/3/16 0.062648691 1079
2000/3/17 -0.043283582 1079
2000/3/20 0.010920437 1079
2000/3/21 -0.010030864 1079
2000/3/22 0.01948558 1079
2000/3/23 0.064220183 1079
2000/3/24 -0.01867816 1079
2000/3/27 -0.004392386 1079
2000/3/28 -0.0125 1079
2000/3/29 -0.034996279 1079
2000/3/30 0.010802469 1079
2000/3/31 0.011450382 1079
;
run;
proc iml;
use have;
read all var{yymm ret};
close;
levels=t(unique(yymm));
cum_ret= j(nrow(levels),1);
do i=1 to nrow(levels);
  idx=loc(yymm=levels[i]);
  cum_ret[i]=cuprod(ret[idx]+1)[ncol(idx)]-1;
end;

create want var{levels cum_ret};
append;
close;
quit;
vickyCh
Obsidian | Level 7

I know nothing about proc iml.

In addition to cum_ret, could I keep the last row of each month of data (data=have) as well?

Meanwhile, there are many different entities to be considered.

art297
Opal | Level 21

I get a different cumulative sum then you do for March, but is the following what you are trying to get?:

data need;
  set have;
  yymm=100*year(date)+month(date);
run;

data want;
  set need;
  by entity yymm;
  retain cumret;
  amt=1+ret;
  if first.yymm then cumret=amt;
  else cumret=cumret*amt;
  if last.yymm then do;
    cumret=cumret-1;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

vickyCh
Obsidian | Level 7

Thank you for your reply, Art. Your answers are correct. (Mine for March was a typo.)
The ends justify the means. Although your coding was not the same as what was on my mind, the results were what I wanted.

Ksharp
Super User
I think Art gave what you need.But be careful. if ret have some missing value Art 's code would not work.
vickyCh
Obsidian | Level 7

As a matter of fact, I would very much like to know how you cope with the question I asked with Proc IML.

Ksharp
Super User

IML is broadly used in Finance or Security field , and IML have build-in function CUPROD() which is just for your question ,So let me connect to IML quickly . But for your scenario , data step is good enough .

vickyCh
Obsidian | Level 7

Many thanks.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 3109 views
  • 3 likes
  • 4 in conversation