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
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!
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!
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: