Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Home
- /
- About jsmith94
11-27-2017
jsmith94
Calcite | Level 5
Member since
11-13-2017
- 6 Posts
- 0 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by jsmith94
Subject Views Posted 2110 11-15-2017 06:44 PM 3729 11-15-2017 06:41 PM 2170 11-15-2017 11:35 AM 3746 11-15-2017 10:36 AM 3771 11-14-2017 10:00 AM 3821 11-13-2017 05:18 PM -
Activity Feed for jsmith94
- Posted Re: How to create a Last-in First-out (LIFO) Algorithm Using Hash Tables on SAS Programming. 11-15-2017 06:44 PM
- Posted Re: Calculating FIFO profit using Hash of Hashes on SAS Programming. 11-15-2017 06:41 PM
- Posted How to create a Last-in First-out (LIFO) Algorithm Using Hash Tables on SAS Programming. 11-15-2017 11:35 AM
- Posted Re: Calculating FIFO profit using Hash of Hashes on SAS Programming. 11-15-2017 10:36 AM
- Posted Re: Calculating FIFO profit using Hash of Hashes on SAS Programming. 11-14-2017 10:00 AM
- Posted Calculating FIFO profit using Hash of Hashes on SAS Programming. 11-13-2017 05:18 PM
11-15-2017
06:44 PM
Thank you so much! I had come up with solutions for FIFO (less efficient than yours and I wasn't confident about them) but I could not figure out LIFO. You've saved me a huge amount of time!
... View more
11-15-2017
06:41 PM
Thank you so much for this solution! It works beautifully and is extremely efficient.
... View more
11-15-2017
11:35 AM
Hi, I have a large dataset of trading data that looks like this: data have; infile datalines truncover dlm=',' dsd; input cusip:$char9. trade_date:mmddyy10. execution_time:time8. buy_sell:$char1. (quantity execution_price trade_id) (:best32.); format execution_time time8. trade_date mmddyy10.; datalines; 010831BW4,09/12/2013,12:30:44,B,45,103.133,1325534595 010831BW4,09/12/2013,14:56:13,B,500,103.303,1325536968 010831BW4,09/17/2013,13:14:35,S,25,105.244,1326014939 010831BW4,09/17/2013,15:40:12,S,50,105.369,1326017794 010831BW4,09/17/2013,16:04:21,S,25,105.244,1326018222 010831BW4,09/18/2013,10:12:59,S,50,105.369,1326121154 010831BW4,09/18/2013,11:50:21,S,350,105.015,1326123005 010831BW4,09/20/2013,11:00:41,B,50,106.308,1326341521 010831BW4,09/20/2013,11:00:41,S,50,106.308,1326341522 010831BY0,09/12/2013,12:30:44,B,1075,99.592,1325534596 010831BY0,09/12/2013,16:51:56,B,350,99.777,1325547689 010831BY0,09/13/2013,11:01:07,S,20,100.25,1325642219 010831BY0,09/13/2013,11:03:05,S,330,100.44,1325642259 010831BY0,10/02/2013,11:07:37,B,50,101.117,1327522086 010831BY0,10/03/2013,11:54:05,S,50,104.218,1327632245 ; run; I am trying to match the buy observations with the sell observations (distinguished by the Buy_Sell variable) within each CUSIP using a Last-in Last-out methodology. In other words, each time there is a sale in a CUSIP, I want the quantity most recently purchased in that CUSIP matched to the sale. If more is bought than sold (or sold than bought), then after the final buy or sell observation (whichever comes first) I want the extra quantity discarded and for the program to move on to the next CUSIP. I want my final table to look like this: CUSIP Quantity b_id bdate btime bprice s_id sdate stime sprice 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326014939 17-Sep-13 13:14:35 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326018222 17-Sep-13 16:04:21 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326121154 18-Sep-13 10:12:59 105.369 010831BW4 350 1325536968 12-Sep-13 14:56:13 103.303 1326123005 18-Sep-13 11:50:21 105.015 010831BW4 50 1326341521 20-Sep-13 11:00:41 106.308 1326341522 20-Sep-13 11:00:41 106.308 010831BY0 20 1325547689 12-Sep-13 16:51:56 $99.78 1325642219 13-Sep-13 11:01:07 100.25 010831BY0 330 1325547689 12-Sep-13 16:51:56 $99.78 1325642259 13-Sep-13 11:03:05 100.44 010831BY1 50 1327522086 2-Oct-13 11:07:37 101.117 1327632245 3-Oct-13 11:54:05 104.218 In order to achieve this, I am using a hash of hashes that changes for each CUSIP and is made up of hash tables and hash iterators for the buy and sell observations. Each time there is a new CUSIP, the two hash tables take all of the buy and sell observations and output them together based on the order that they were brought into the hash table and the quantities that they have. Once the hash tables run out of buy or sell observations in a CUSIP, the additional observations are discarded and the the program moves on to the next CUSIP. Here is my current code: data want; set have; if _n_=1 then do; declare hash hoh (); hoh.definekey('cusip'); hoh.definedata('cusip','buys','sells','buy','sell'); hoh.definedone(); declare hash buys; declare hash sells; declare hiter buy; declare hiter sell; end; if hoh.find() ne 0 then do; length tid 8 b_id bdate btime bprice bquantity quantity s_id sdate stime sprice squantity 8; buys=_new_ hash(ordered:'a'); buys.defineKey('tid'); buys.defineData('bdate','btime','bprice','bquantity','b_id', 'b_account', 'b_source', 'b_cusip', 'in14'); buys.defineDone(); sells=_new_ hash(ordered:'a'); sells.defineKey('tid'); sells.defineData('sdate','stime','sprice','squantity','s_id','s_account', 's_cusip'); sells.defineDone(); buy=_new_ hiter('buys'); sell=_new_ hiter('sells'); inventory=0; end; tid+1; if buy_sell="B" then do; bdate=trade_date; btime=execution_time; bprice=execution_price; bquantity=quantity; b_id=trade_id; b_account=account_number2; b_source=source; b_cusip=cusip; in14=in14; inventory+quantity; buys.add(); end; else do; if inventory=0 then delete; if quantity>inventory then quantity=inventory; sdate=trade_date; stime=execution_time; sprice=execution_price; squantity=quantity; s_id=trade_id; s_account=account_number2; s_cusip=cusip; inventory+(-1*quantity); sells.add(); end; keep bdate b_account b_source b_cusip b_id bdate btime bprice quantity sdate stime sprice s_id s_account s_cusip in14; format bdate sdate date9. bprice sprice dollar8.4 btime stime time12.; brc=buy.last(); src=sell.first(); do while(brc=0 and src=0); select; when (bquantity < squantity) do; temp=squantity-bquantity; quantity=bquantity; output; squantity=temp; brc=buy.prev(); end; when (bquantity=squantity) do; quantity=bquantity; output; brc=buy.prev(); src=sell.next(); end; when (bquantity > squantity) do; temp=bquantity-squantity; quantity=squantity; output; bquantity=temp; src=sell.next(); end; otherwise; end; end; rc=hoh.replace(); run; When I run this code, the output is all over the place. Usually the output begins correct in each CUSIP, and gets further off as it goes along. It gets especially bad when more is bought in a CUSIP than sold and some quantity needs to be discarded. The output also contains many observations that are full duplicates of previous observations, and uses observations even after their quantity has been used up and the hash table was supposed to move on to the next observation. For the dataset above, this is the output that my code gives: CUSIP Quantity b_id bdate btime bprice s_id sdate stime sprice 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326014939 17-Sep-13 13:14:35 105.244 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326014939 17-Sep-13 13:14:35 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326014939 17-Sep-13 13:14:35 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326018222 17-Sep-13 16:04:21 105.244 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326014939 17-Sep-13 13:14:35 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326018222 17-Sep-13 16:04:21 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326121154 18-Sep-13 10:12:59 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326014939 17-Sep-13 13:14:35 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326018222 17-Sep-13 16:04:21 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326121154 18-Sep-13 10:12:59 105.369 010831BW4 350 1325536968 12-Sep-13 14:56:13 103.303 1326123005 18-Sep-13 11:50:21 105.015 010831BW4 25 1326341521 20-Sep-13 11:00:41 106.308 1326014939 17-Sep-13 13:14:35 105.244 010831BW4 25 1326341521 20-Sep-13 11:00:41 106.308 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326018222 17-Sep-13 16:04:21 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326121154 18-Sep-13 10:12:59 105.369 010831BW4 350 1325536968 12-Sep-13 14:56:13 103.303 1326123005 18-Sep-13 11:50:21 105.015 010831BW4 25 1326341521 20-Sep-13 11:00:41 106.308 1326014939 17-Sep-13 13:14:35 105.244 010831BW4 25 1326341521 20-Sep-13 11:00:41 106.308 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326017794 17-Sep-13 15:40:12 105.369 010831BW4 25 1325536968 12-Sep-13 14:56:13 103.303 1326018222 17-Sep-13 16:04:21 105.244 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326121154 18-Sep-13 10:12:59 105.369 010831BW4 350 1325536968 12-Sep-13 14:56:13 103.303 1326123005 18-Sep-13 11:50:21 105.015 010831BW4 50 1325536968 12-Sep-13 14:56:13 103.303 1326341522 20-Sep-13 11:00:41 106.308 010831BY0 20 1325547689 12-Sep-13 16:51:56 $99.78 1325642219 13-Sep-13 11:01:07 100.25 010831BY0 20 1325547689 12-Sep-13 16:51:56 $99.78 1325642219 13-Sep-13 11:01:07 100.25 010831BY0 330 1325547689 12-Sep-13 16:51:56 $99.78 1325642259 13-Sep-13 11:03:05 100.44 010831BY0 20 1327522086 2-Oct-13 11:07:37 101.117 1325642219 13-Sep-13 11:01:07 100.25 010831BY0 30 1327522086 2-Oct-13 11:07:37 101.117 1325642259 13-Sep-13 11:03:05 100.44 010831BY0 300 1325547689 12-Sep-13 16:51:56 $99.78 1325642259 13-Sep-13 11:03:05 100.44 010831BY0 20 1327522086 2-Oct-13 11:07:37 101.117 1325642219 13-Sep-13 11:01:07 100.25 010831BY0 30 1327522086 2-Oct-13 11:07:37 101.117 1325642259 13-Sep-13 11:03:05 100.44 010831BY0 300 1325547689 12-Sep-13 16:51:56 $99.78 1325642259 13-Sep-13 11:03:05 100.44 010831BY0 50 1325547689 12-Sep-13 16:51:56 $99.78 1327632245 3-Oct-13 11:54:05 104.218 As you can see, it has many more observations than it should - some full duplicates of previous observations, some that are repeats of previous values on only the Buy or Sell side. I think the problem is somewhere in how I iterate through my hash table, but I have been unable to find the bug. Thank you for reading through and please let me know if you can figure out what I am doing wrong!
... View more
11-15-2017
10:36 AM
Thanks for your reply Ksharp! It's clever, but it only works if the dataset is relatively small. The total quantity in my dataset is in the billions, which using your algorithm would create billions of observations in two datasets and then merge them back together. This would take a long time and might not work at all because of the amount of space and memory required. My hash table is able to process and match my data together in seconds - I'm really just trying to find the bug in that piece of code that's creating duplicates for me in the output. Thanks though!
... View more
11-14-2017
10:00 AM
Sorry for the lack of information. Here is a data statement: Data have; Input Cusip $ Trade_Date mmddyy10. Execution_Time time9. Execution_Price Quantity Buy_Sell $ Account_Number2 Source Trade_ID In14; datalines; A62 05/06/2013 11:46:58 100.1 100 B 602 1 135 1 A62 05/06/2013 12:23:10 100.1 250 B 352 3 320 1 A62 05/06/2013 15:09:02 100.1 150 B 602 1 616 1 A62 06/07/2013 14:48:28 99.854 500 S 176 3 065 0 A70 05/06/2013 11:47:53 100.1 150 B 602 1 287 1 A70 05/06/2013 12:23:50 100.1 250 B 352 3 326 1 A70 05/06/2013 15:09:58 100.1 100 B 602 1 626 1 A70 05/14/2013 11:58:52 100.028 715 B 161 3 251 1 A70 05/20/2013 12:18:25 100.738 1215 S 176 3 628 0 A88 05/06/2013 11:48:44 100.1 150 B 602 1 290 1 A88 05/06/2013 12:24:29 100.1 250 B 352 3 345 1 A88 05/06/2013 15:10:38 100.1 100 B 602 1 643 1 A88 05/20/2013 12:54:53 100.699 500 S 176 3 165 0 A96 05/06/2013 11:49:21 100.1 135 B 602 1 291 1 A96 05/06/2013 12:25:01 100.1 140 B 352 3 348 1 A96 06/18/2013 16:04:55 98.257 275 S 176 3 466 0 B20 05/06/2013 11:51:16 100.1 150 B 602 1 292 1 B20 05/06/2013 15:51:57 100.1 80 B 602 1 268 1 B20 06/27/2013 09:48:19 93.6 230 S 176 3 181 0 B38 05/03/2013 15:50:05 100.1 100 B 549 3 921 1 B38 05/06/2013 11:52:02 100.1 110 B 602 1 899 1 B38 05/20/2013 16:55:36 100.375 210 S 176 3 868 0 L55 03/07/2014 11:24:32 100.05 500 B 398 2 424 1 L55 03/07/2014 13:00:45 100 500 B 793 3 953 1 L55 03/11/2014 12:07:02 100.239 75 S 176 3 716 0 ; run; The objective is to match the first quantity bought with the first quantity sold in each CUSIP, disregarding any excess quantity on the buy or sell side that can't be matched. Then, the output dataset should have all of the data from the buy observations and all of the data from the sell observation matched alongside, like this: SAS Output b_id bdate btime bprice quantity s_id sdate stime sprice In14 b_account b_source b_cusip s_account s_cusip 135 06MAY2013 11:46:58 100.1000 100 65 07JUN2013 14:48:28 $99.8540 1 602 1 A62 176 A62 320 06MAY2013 12:23:10 100.1000 250 65 07JUN2013 14:48:28 $99.8540 1 352 3 A62 176 A62 616 06MAY2013 15:09:02 100.1000 150 65 07JUN2013 14:48:28 $99.8540 1 602 1 A62 176 A62 287 06MAY2013 11:47:53 100.1000 150 628 20MAY2013 12:18:25 100.7380 1 602 1 A70 176 A70 326 06MAY2013 12:23:50 100.1000 250 628 20MAY2013 12:18:25 100.7380 1 352 3 A70 176 A70 626 06MAY2013 15:09:58 100.1000 100 628 20MAY2013 12:18:25 100.7380 1 602 1 A70 176 A70 251 14MAY2013 11:58:52 100.0280 715 628 20MAY2013 12:18:25 100.7380 1 161 3 A70 176 A70 290 06MAY2013 11:48:44 100.1000 150 165 20MAY2013 12:54:53 100.6990 1 602 1 A88 176 A88 345 06MAY2013 12:24:29 100.1000 250 165 20MAY2013 12:54:53 100.6990 1 352 3 A88 176 A88 643 06MAY2013 15:10:38 100.1000 100 165 20MAY2013 12:54:53 100.6990 1 602 1 A88 176 A88 291 06MAY2013 11:49:21 100.1000 135 466 18JUN2013 16:04:55 $98.2570 1 602 1 A96 176 A96 348 06MAY2013 12:25:01 100.1000 140 466 18JUN2013 16:04:55 $98.2570 1 352 3 A96 176 A96 292 06MAY2013 11:51:16 100.1000 150 181 27JUN2013 9:48:19 $93.6000 1 602 1 B20 176 B20 268 06MAY2013 15:51:57 100.1000 80 181 27JUN2013 9:48:19 $93.6000 1 602 1 B20 176 B20 921 03MAY2013 15:50:05 100.1000 100 868 20MAY2013 16:55:36 100.3750 1 549 3 B38 176 B38 899 06MAY2013 11:52:02 100.1000 110 868 20MAY2013 16:55:36 100.3750 1 602 1 B38 176 B38 424 07MAR2014 11:24:32 100.0500 75 716 11MAR2014 12:07:02 100.2390 1 398 2 L55 176 L55 Let me know if there is any other information that would be useful to know. The basic logic is to create a Hash of Hashes that changes for each CUSIP, and contains references to a Buys hash table, a Sells hash table, a Buy iterator, and a Sell iterator. The hash tables take all of the buy and sell observations, and output them together based on the order that they were brought into the hash table and the quantities that they have. Once the hash tables run out of buy or sell observations in a CUSIP, the additional observations are discarded and the the program moves on to the next CUSIP. This is largely working - just with a massive number of duplicates.
... View more
11-13-2017
05:18 PM
I have a large dataset with trading data from which I am trying to calculate profit with a FIFO (first-in first-out) methodology in Base SAS 9.4. In other words, I want the first CUSIPs bought to be matched to the first CUSIPs sold, regardless of the date. In my final dataset, I want all of the variables from the "Buy" observation of the input dataset to show up alongside all of the variables from the "Sell" observation of the input dataset. If more is bought than sold or sold than bought in a given CUSIP, then the excess should be excluded from the final dataset. Furthermore, if more is sold than has been bought at that time, then the excess sell quantity should be disregarded. A few rows of my input dataset are here: CUSIP Date Time Price Quantity Buy / Sell Account Source Trade ID In14 (Dummy) A62 5/6/2013 11:46:58 AM 100.1 100 B 602 1 135 1 A62 5/6/2013 12:23:10 PM 100.1 250 B 352 3 320 1 A62 5/6/2013 3:09:02 PM 100.1 150 B 602 1 616 1 A62 6/7/2013 2:48:28 PM 99.854 500 S 176 3 065 0 A70 5/6/2013 11:47:53 AM 100.1 150 B 602 1 287 1 A70 5/6/2013 12:23:50 PM 100.1 250 B 352 3 326 1 A70 5/6/2013 3:09:58 PM 100.1 100 B 602 1 626 1 A70 5/14/2013 11:58:52 AM 100.028 715 B 161 3 251 1 A70 5/20/2013 12:18:25 PM 100.738 1,215 S 176 3 628 0 A88 5/6/2013 11:48:44 AM 100.1 150 B 602 1 290 1 A88 5/6/2013 12:24:29 PM 100.1 250 B 352 3 345 1 A88 5/6/2013 3:10:38 PM 100.1 100 B 602 1 643 1 A88 5/20/2013 12:54:53 PM 100.699 500 S 176 3 165 0 A96 5/6/2013 11:49:21 AM 100.1 135 B 602 1 291 1 A96 5/6/2013 12:25:01 PM 100.1 140 B 352 3 348 1 A96 6/18/2013 4:04:55 PM 98.257 275 S 176 3 466 0 B20 5/6/2013 11:51:16 AM 100.1 150 B 602 1 292 1 B20 5/6/2013 3:51:57 PM 100.1 80 B 602 1 268 1 B20 6/27/2013 9:48:19 AM 93.6 230 S 176 3 181 0 B38 5/3/2013 3:50:05 PM 100.1 100 B 549 3 921 1 B38 5/6/2013 11:52:02 AM 100.1 110 B 602 1 899 1 B38 5/20/2013 4:55:36 PM 100.375 210 S 176 3 868 0 L55 3/7/2014 11:24:32 AM 100.05 500 B 398 2 424 1 L55 3/7/2014 1:00:45 PM 100 500 B 793 3 953 1 L55 3/11/2014 12:07:02 PM 100.239 75 S 176 3 716 0 In order to create the final table I described above, I have written the following code that creates a Hash of Hashes for each CUSIP that uses two other Hash Tables, one for buys, and one for sells. The code is here: data want; set have; if _n_=1 then do; declare hash hoh (); hoh.definekey('cusip'); hoh.definedata('cusip','buys','sells','buy','sell'); hoh.definedone(); declare hash buys; declare hash sells; declare hiter buy; declare hiter sell; end; if hoh.find() ne 0 then do; length tid 8 b_id bdate btime bprice bquantity quantity s_id sdate stime sprice squantity 8; buys=_new_ hash(ordered:'a'); buys.defineKey('tid'); buys.defineData('bdate','btime','bprice','bquantity','b_id', 'b_account', 'b_source', 'b_cusip', 'in14'); buys.defineDone(); sells=_new_ hash(ordered:'a'); sells.defineKey('tid'); sells.defineData('sdate','stime','sprice','squantity','s_id','s_account', 's_cusip'); sells.defineDone(); buy=_new_ hiter('buys'); sell=_new_ hiter('sells'); inventory=0; end; tid+1; if buy_sell="B" then do; bdate=trade_date; btime=execution_time; bprice=execution_price; bquantity=quantity; b_id=trade_id; b_account=account_number2; b_source=source; b_cusip=cusip; in14=in14; inventory+quantity; buys.add(); end; else do; if inventory=0 then delete; if quantity>inventory then quantity=inventory; sdate=trade_date; stime=execution_time; sprice=execution_price; squantity=quantity; s_id=trade_id; s_account=account_number2; s_cusip=cusip; inventory+(-1*quantity); sells.add(); end; keep bdate b_account b_source b_cusip b_id bdate btime bprice quantity sdate stime sprice s_id s_account s_cusip in14; format bdate sdate date9. bprice sprice dollar8.4 btime stime time12.; brc=buy.first(); src=sell.first(); do while(brc=0 and src=0); select; when (bquantity < squantity) do; temp=squantity-bquantity; quantity=bquantity; output; squantity=temp; brc=buy.next(); end; when (bquantity=squantity) do; quantity=bquantity; output; brc=buy.next(); src=sell.next(); end; when (bquantity > squantity) do; temp=bquantity-squantity; quantity=squantity; output; bquantity=temp; src=sell.next(); end; otherwise; end; end; rc=hoh.replace(); run; The problem is that I get tons of duplicate entries...my final dataset has almost 30 times the number of observations as it does once I remove duplicates. Any idea how I can stop the hash table from creating these duplicates? Once I remove the duplicates it appears to actually have correctly matched buys and sells within each CUSIP and discarded excess buy and sell quantity, but I would feel more confident about this if I wasn't getting these duplicates. Thanks so much for reading through!
... View more
