Monthly returns

[liuo1]

Hi SAS community,

I have a dataset where I have end of month bond prices. What I need is the corresponding month to month return on these prices. I have made a short dataset example of the dataset requiring this operation:

data work.test;	
    input cusip $ bond_sym_id $ company_symbol $ month $ trd_exctn_tm $ entrd_vol_qt rptd_pr accrued_coupon;
    format month YYMMD7. trd_exctn_tm TIME8. entrd_vol_qt 14.2 rptd_pr 11.6 accrued_coupon 14.2;
    datalines;
000325AA8 AAFM.GA AAFM 2002-07 14:08:16 100000 100.750 0.15
000325AA8 AAFM.GA AAFM 2002-08 16:06:47 3060000 100.500 0.11
000325AA8 AAFM.GA AAFM 2002-10 14:54:08 150000 101.250 0.01
000325AA8 AAFM.GA AAFM 2002-11 15:30:59 350000 100.375 0.02
000325AA8 AAFM.GA AAFM 2002-12 11:57:59 150000 100.125 0.00
000325AA8 AAFM.GA AAFM 2007-04 13:54:24 100000 102.875 0.00
000336AE7 GFR.GA GFR 2002-07 15:53:58 10000 98.000 0.08
000336AE7 GFR.GA GFR 2002-08 10:57:51 10000 97.693 0.01
000336AE7 GFR.GA GFR 2002-09 16:07:00 50000 91.480 0.02
000336AE7 GFR.GA GFR 2002-09 16:07:00 50000 91.356 0.03
000336AE7 GFR.GA GFR 2002-10 13:57:01 10000 96.000 0.02
000336AE7 GFR.GA GFR 2002-11 13:29:26 4000 103.431 0.01
000336AE7 GFR.GA GFR 2002-12 13:14:00 25000 97.706 0.00
000336AE7 GFR.GA GFR 2003-01 10:45:02 40000 104.286 0.05
000336AE7 GFR.GA GFR 2003-03 15:07:50 10000 91.362 0.07
000336AE7 GFR.GA GFR 2003-04 17:36:59 2000000 90.250 0.01
000336AE7 GFR.GA GFR 2003-05 13:41:47 8000 91.250 0.05
000336AE7 GFR.GA GFR 2003-06 14:58:00 80000 99.500 0.03
000336AE7 GFR.GA GFR 2003-07 10:41:42 13000 98.821 0.02
000336AE7 GFR.GA GFR 2003-08 15:59:01 8000 99.375 0.14
000336AE7 GFR.GA GFR 2003-09 15:36:53 1950000 96.000 0.10
000336AE7 GFR.GA GFR 2003-10 17:02:00 10000 102.500 0.00
000336AE7 GFR.GA GFR 2003-11 15:38:00 20000 98.000 0.04
000336AE7 GFR.GA GFR 2003-11 15:38:00 20000 98.000 0.05
000336AE7 GFR.GA GFR 2004-01 16:32:43 8300000 108.250 0.05
000336AE7 GFR.GA GFR 2004-02 12:01:00 500000 107.000 0.05
000336AE7 GFR.GA GFR 2004-03 16:57:00 35000 106.000 0.00
000336AE7 GFR.GA GFR 2004-04 15:33:00 10000 103.000 0.01
000336AE7 GFR.GA GFR 2004-05 13:55:00 10000 105.246 0.02
000336AE7 GFR.GA GFR 2004-06 9:00:30 340000 104.764 0.03
000336AE7 GFR.GA GFR 2004-07 14:31:00 2000 104.000 0.04
000336AE7 GFR.GA GFR 2004-08 15:05:00 10000 103.000 0.05
000336AE7 GFR.GA GFR 2004-09 12:15:00 1000 104.500 0.06
000336AE7 GFR.GA GFR 2004-10 13:45:00 15000 106.000 0.02
000336AE7 GFR.GA GFR 2004-11 9:55:00 20000 106.000 0.03
000336AE7 GFR.GA GFR 2004-12 12:36:00 25000 107.250 0.05
000336AE7 GFR.GA GFR 2005-01 15:16:06 10000 104.125 0.00
000336AE7 GFR.GA GFR 2005-02 11:29:10 25000 104.000 0.01
000336AE7 GFR.GA GFR 2005-03 8:16:39 31000 105.259 0.03
000336AE7 GFR.GA GFR 2005-04 11:43:35 50000 103.013 0.07
000336AE7 GFR.GA GFR 2005-04 11:43:35 50000 103.013 0.09
000336AE7 GFR.GA GFR 2005-05 13:37:00 50000 103.113 0.01
000336AE7 GFR.GA GFR 2005-05 13:37:00 50000 103.313 0.02
000336AE7 GFR.GA GFR 2005-06 15:15:10 16000000 105.382 0.12
000336AE7 GFR.GA GFR 2005-07 11:44:00 15000 104.656 0.10
000336AE7 GFR.GA GFR 2005-08 13:30:00 40000 103.500 0.01
000336AE7 GFR.GA GFR 2005-09 13:05:11 20000 101.500 0.04
000336AE7 GFR.GA GFR 2005-10 15:43:00 15000 102.200 0.06
000336AE7 GFR.GA GFR 2005-10 15:43:00 15000 102.800 0.07
000336AE7 GFR.GA GFR 2005-11 12:26:00 3000 102.292 0.07
;
run;

The rptd_pr is the end of month price. The month to month return for the bond IDed by cusip is calculated taking the percentage change of last months price plus accrued_coupon to this months price plus accrued_coupon. For months without a previous trading month there would be no return. Any help on this would be greatly appreciated! I am struggling to find out how I can condition the return calculation to find the corresponding end of month price just one month prior (not just the row above since they are not traded every month in a lot of cases).

My current attempt is:

data want1;
set work.test;
by cusip month notsorted;
dirty_price=rptd_pr+accrued_coupon;
ret_eom=dirty_price/lag(dirty_price)-1;
if first.cusip then ret_eom=.;
run;

However, this does not take into account the months having to be the previous month.

Kind regards,

liuo1

[liuo1 · Accepted solution]

 Hi SAS community,

I was able to get the result I wanted using this code:

data work.test;	
    input cusip $ bond_sym_id $ company_symbol $ month $ trd_exctn_tm TIME8. entrd_vol_qt rptd_pr accrued_coupon;
    format month YYMMD7. trd_exctn_tm TIME8. entrd_vol_qt 14.2 rptd_pr 11.6 accrued_coupon 14.2;
    datalines;
000325AA8 AAFM.GA AAFM 2002-07 14:08:16 100000 100.750 0.15
000325AA8 AAFM.GA AAFM 2002-08 16:06:47 3060000 100.500 0.11
000325AA8 AAFM.GA AAFM 2002-10 14:54:08 150000 101.250 0.01
000325AA8 AAFM.GA AAFM 2002-11 15:30:59 350000 100.375 0.02
000325AA8 AAFM.GA AAFM 2002-12 11:57:59 150000 100.125 0.00
000325AA8 AAFM.GA AAFM 2007-04 13:54:24 100000 102.875 0.00
000336AE7 GFR.GA GFR 2002-07 15:53:58 10000 98.000 0.08
000336AE7 GFR.GA GFR 2002-08 10:57:51 10000 97.693 0.01
000336AE7 GFR.GA GFR 2002-09 16:07:00 50000 91.480 0.02
000336AE7 GFR.GA GFR 2002-09 16:07:00 50000 91.356 0.03
000336AE7 GFR.GA GFR 2002-10 13:57:01 10000 96.000 0.02
000336AE7 GFR.GA GFR 2002-11 13:29:26 4000 103.431 0.01
000336AE7 GFR.GA GFR 2002-12 13:14:00 25000 97.706 0.00
000336AE7 GFR.GA GFR 2003-01 10:45:02 40000 104.286 0.05
000336AE7 GFR.GA GFR 2003-03 15:07:50 10000 91.362 0.07
000336AE7 GFR.GA GFR 2003-04 17:36:59 2000000 90.250 0.01
000336AE7 GFR.GA GFR 2003-05 13:41:47 8000 91.250 0.05
000336AE7 GFR.GA GFR 2003-06 14:58:00 80000 99.500 0.03
000336AE7 GFR.GA GFR 2003-07 10:41:42 13000 98.821 0.02
000336AE7 GFR.GA GFR 2003-08 15:59:01 8000 99.375 0.14
000336AE7 GFR.GA GFR 2003-09 15:36:53 1950000 96.000 0.10
000336AE7 GFR.GA GFR 2003-10 17:02:00 10000 102.500 0.00
000336AE7 GFR.GA GFR 2003-11 15:38:00 20000 98.000 0.04
000336AE7 GFR.GA GFR 2003-11 15:38:00 20000 98.000 0.05
000336AE7 GFR.GA GFR 2004-01 16:32:43 8300000 108.250 0.05
000336AE7 GFR.GA GFR 2004-02 12:01:00 500000 107.000 0.05
000336AE7 GFR.GA GFR 2004-03 16:57:00 35000 106.000 0.00
000336AE7 GFR.GA GFR 2004-04 15:33:00 10000 103.000 0.01
000336AE7 GFR.GA GFR 2004-05 13:55:00 10000 105.246 0.02
000336AE7 GFR.GA GFR 2004-06 9:00:30 340000 104.764 0.03
000336AE7 GFR.GA GFR 2004-07 14:31:00 2000 104.000 0.04
000336AE7 GFR.GA GFR 2004-08 15:05:00 10000 103.000 0.05
000336AE7 GFR.GA GFR 2004-09 12:15:00 1000 104.500 0.06
000336AE7 GFR.GA GFR 2004-10 13:45:00 15000 106.000 0.02
000336AE7 GFR.GA GFR 2004-11 9:55:00 20000 106.000 0.03
000336AE7 GFR.GA GFR 2004-12 12:36:00 25000 107.250 0.05
000336AE7 GFR.GA GFR 2005-01 15:16:06 10000 104.125 0.00
000336AE7 GFR.GA GFR 2005-02 11:29:10 25000 104.000 0.01
000336AE7 GFR.GA GFR 2005-03 8:16:39 31000 105.259 0.03
000336AE7 GFR.GA GFR 2005-04 11:43:35 50000 103.013 0.07
000336AE7 GFR.GA GFR 2005-04 11:43:35 50000 103.013 0.09
000336AE7 GFR.GA GFR 2005-05 13:37:00 50000 103.113 0.01
000336AE7 GFR.GA GFR 2005-05 13:37:00 50000 103.313 0.02
000336AE7 GFR.GA GFR 2005-06 15:15:10 16000000 105.382 0.12
000336AE7 GFR.GA GFR 2005-07 11:44:00 15000 104.656 0.10
000336AE7 GFR.GA GFR 2005-08 13:30:00 40000 103.500 0.01
000336AE7 GFR.GA GFR 2005-09 13:05:11 20000 101.500 0.04
000336AE7 GFR.GA GFR 2005-10 15:43:00 15000 102.200 0.06
000336AE7 GFR.GA GFR 2005-10 15:43:00 15000 102.800 0.07
000336AE7 GFR.GA GFR 2005-11 12:26:00 3000 102.292 0.07
;
run;

data work.test;
   set work.test;
   month2= input(month,anydtdte32.);
   Format month2 yymmd7.;
run;

data work.test;
	set work.test (drop=month);
run;

proc print data=work.test;
run;

proc contents data=work.test; run;

data want1;
set work.test;
by cusip month2 notsorted;
dirty_price=rptd_pr+accrued_coupon;
time_diff=INTCK("month",month2,lag(month2));
ret_eom=dirty_price/lag(dirty_price)-1;
if first.cusip then ret_eom=.;
if time_diff ~= -1 then ret_eom=.;
run;

Thanks!