proc expand convert from day to month

[vickyCh]

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.

[Reeza]

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]

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]

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]

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]

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]

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 · Accepted solution]

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]

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]

I think Art gave what you need.But be careful. if ret have some missing value Art 's code would not work.

[vickyCh]

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

[Ksharp]

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]

Many thanks.