Last given price of each month in each year

[liuo1]

Hi SAS community,

I am trying to obtain the last price in each calendar month for each year of bond prices. So that I will have a time series of monthly prices (being the last price that given month) for each bond (by cusip). Would anyone know how to tackle coding this? I have thought of using the LAST. function but do not really know how to get the final product. An example of the variables is:

where trd_exctn_dt is the date of the trade and trd_exctn_tm is the time. The price is given by the rptd_pr column and unique identifier for the bond is the cusip.

Any help would be greatly appreciated!

Thanks,

liuo1

[andreas_lds]

Please post data in usable form: a data step using datalines or cards.

First steps:

• trd_exctn_dt should be a sas date, not a string
• trd_exctn_tm should be a sas time value
• add at least one more cusip

[liuo1]

Hi, thanks for your reply! I have attempted to manually create an example on SAS where there are two cusips. Here is the data:

data work.transactions;	
    input cusip $ company_symbol $ trd_exctn_dt date9. trd_exctn_tm time9. entrd_vol_qt rptd_pr;
    format trd_exctn_dt date9. trd_exctn_tm time8. rptd_pr 12.3;
    datalines;
000111AA1 AAAA 03JUL2002 08:43:33 2000000 99.000
000111AA1 AAAA 07JUL2002 18:41:13 1000000 100.000
000111AA1 AAAA 24JUL2002 11:00:00 3400000 118.000
000111AA1 AAAA 24JUL2002 22:40:25 150000 98.000
000111AA1 AAAA 03AUG2002 08:43:33 2000000 100.000
000111AA1 AAAA 15AUG2002 08:43:33 2000000 115.000
000111AA1 AAAA 07SEP2002 18:41:13 1000000 116.000
000111AA1 AAAA 24NOV2002 11:00:00 3400000 117.000
000111AA1 AAAA 24DEC2002 22:40:25 150000 110.000
000111AA1 AAAA 03JAN2003 08:43:33 2000000 100.000
000111AA1 AAAA 07FEB2003 18:41:13 1000000 120.000
000111AA1 AAAA 24MAR2003 11:00:00 3400000 130.000
000111AA1 AAAA 24JAN2004 22:40:25 150000 140.500
000111AA2 AAAB 03JUL2002 08:43:33 2000000 25.120
000111AA2 AAAB 07JUL2002 18:41:13 1000000 26.000
000111AA2 AAAB 24JUL2002 11:00:00 3400000 25.000
000111AA2 AAAB 24JUL2002 22:40:25 150000 27.000
000111AA2 AAAB 03AUG2002 08:43:33 2000000 28.500
000111AA2 AAAB 15AUG2002 08:43:33 2000000 28.000
000111AA2 AAAB 07SEP2002 18:41:13 1000000 29.500
000111AA2 AAAB 24NOV2002 11:00:00 3400000 30.000
000111AA2 AAAB 24DEC2002 22:40:25 150000 35.000
000111AA2 AAAB 03JAN2003 08:43:33 2000000 32.000
000111AA2 AAAB 07FEB2003 18:41:13 1000000 31.500
000111AA2 AAAB 24MAR2003 11:00:00 3400000 32.500
000111AA2 AAAB 24JAN2004 22:40:25 150000 34.000
;
run;

proc print data=work.transactions;
run;

I hope this works as an example! Please let me know if I am using the right date and time formats as I am quite new to using SAS. Thanks again!

[Kurt_Bremser · Accepted solution]

In a single SQL:

proc sql;
create table want as
  select
    t1.cusip,
    month(t1.trd_exctn_dt) as month format=yymmd7.,
    rptd_pr
  from have t1
  group by t1.cusip, calculated month
  having t1.trd_exctn_dt = max(t1.trd_exctn_dt)
;
quit;

Untested, for lack of usable data.

[liuo1]

Hi, thanks for your reply! I posted some data that is hopefully useable in the reply above! I tried using the code for that dataset and was getting something looking like this:

Ignoring the cusip being all the same and the month being 1960-01, I think the code correctly selects the last day of the month to pick the price, however if last trade day is the same for two trades, it selects both the trades rather than only one with the latest time. Would you happen to know how to implement this? Thanks again!

[Kurt_Bremser]

Replace

month(t1.trd_exctn_dt)

with

intnx('month',t1.trd_exctn_dt,0,'b')

[liuo1]

That works, thanks!

Using your code, I added the time as well to the having condition:

proc sql;
create table want as
  select
    t1.cusip,
    intnx('month',t1.trd_exctn_dt,0,'b') as month format=yymmd7.,
	trd_exctn_tm,
    rptd_pr
  from work.transactions t1
  group by t1.cusip, calculated month
  having t1.trd_exctn_dt = max(t1.trd_exctn_dt) and trd_exctn_tm = max(trd_exctn_tm)
;
quit;

and was able to get what I was looking for.

Thanks again!