cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
jdmarino
‎09-01-2015
  • 78 Posts
  • 26 Likes Given
  • 0 Solutions
  • 5 Likes Received

Re: Difficult merge/join with huge datasets

by Fluorite | Level 6 in SAS Programming
‎09-04-2014 10:23 AM
‎09-04-2014 10:23 AM
Well, I figured out the failure: I added my options to the top of the .cfg file, but they were being reset (to the lower values) at the bottom of the file!  The hash version is running now, and clearly using nearly all the RAM. ... View more

Re: Difficult merge/join with huge datasets

by Fluorite | Level 6 in SAS Programming
‎09-04-2014 10:10 AM
‎09-04-2014 10:10 AM
>> Why is memsize a fail? I don't know.  I was hoping you did. Another fail: SORTSIZE.  In my .cfg file I have it set to 19G. Does one need admin rights on a Windows machine to set these params?  I don't have admin rights on this machine. ... View more

Re: Difficult merge/join with huge datasets

by Fluorite | Level 6 in SAS Programming
‎09-04-2014 09:06 AM
‎09-04-2014 09:06 AM
My original SQL version took 5 hours. KSharp's SET-BY interleaving took < 1 minute (after a 7 minute sort). I haven't tried Jaap's method, mostly because it seems to me like a manual version of KSharp's. I am trying Hai.Kuo's hash method, but running out of memory, and I don't know why.  The dataset is 7G on my disk, but I have 24G of RAM.  This is the error: ERROR: Hash object added 16252912 items when memory failure occurred. FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase. ERROR: The SAS System stopped processing this step because of insufficient memory. Proc options group=memory reports: Group=MEMORY SORTSIZE=268435456                    Size parameter for sort SUMSIZE=0         Upper limit for data-dependent memory usage during summarization MAXMEMQUERY=0     Maximum amount of memory returned when inquiring as to available space MEMBLKSZ=16777216 Size of memory blocks allocated to support MEMLIB and MEMCACHE options. MEMMAXSZ=2147483648                    Maximum amount of memory allocated to support MEMLIB and MEMCACHE options. LOADMEMSIZE=0     Suggested memory limit for loaded SAS executables MEMSIZE=2147483648                    Specifies the limit on the total amount of memory to be used by the SAS System REALMEMSIZE=0     Limit on the total amount of real memory to be used by the SAS System N I set "-MEMSIZE 19G" in my sasv9.cfg file.  Any ideas why I'm not using all the physical memory? I have 64-bit SAS 9.3 (TS1M1) installed on a Windows 7 machine. FYI: I don't expect the hash method to be efficient because I have only 100 keys (50 tickers x 2 dates) in my real data.  That means for each key I'll be looping/scanning an average of 80,000 (16,000,000 items / 100 keys / 2) elements to find the one I want for each row in Events. ... View more

Re: Difficult merge/join with huge datasets

by Fluorite | Level 6 in SAS Programming
‎08-30-2014 10:27 AM
‎08-30-2014 10:27 AM
This is very interesting because it's so direct.  My data is actually not sorted, but I built an index on (Ticker TradeDate TradeTime) on each, so I think the BY statement will still work.  And I surely need to change the first IF to include TradeDate, like this if first.Ticker or first.TradeDate then call missing(price); Thanks for the reply; I'm excited to try this.  (That should be obvious because I'm replying on a holiday weekend about a work-related problem.) ... View more

Re: Difficult merge/join with huge datasets

by Fluorite | Level 6 in SAS Programming
‎08-30-2014 10:18 AM
‎08-30-2014 10:18 AM
It's good that you posted this idea for future reference.  As it happens, my Prices data is much bigger than my Events data, so this probably won't work for me.  I can break the problem down into chunks (the smallest being a ticker-date), but extracting the chunks may be time-consuming, given that I'd have to wrap a loop around it.  It's worth a test, however. ... View more

Re: Difficult merge/join with huge datasets

by Fluorite | Level 6 in SAS Programming
‎08-29-2014 04:42 PM
‎08-29-2014 04:42 PM
This is the kind of answer I was hoping for -- there are techniques here that are new to me.  The conditional use of Set, pluse the point= option is something I need to learn about. I'll give this a try (next week; I'm burned out) and report back. Thanks for the reply. ... View more

Re: Difficult merge/join with huge datasets

by Fluorite | Level 6 in SAS Programming
‎08-29-2014 04:15 PM
‎08-29-2014 04:15 PM
The results generated by your suggestion are not correct, but thanks for responding. the new results only contain the rows that can be priced with the latest price for each ticker/date pair.  Events that occurred earlier than the latest price get omitted but should not be, because they have a valid price (before the event). TradeDate TradeTime Tickr Qty PriceTime Price 08/01/2013 14:20:00 MSFT 1420 11:59:00 11.59 08/01/2013 15:59:00 MSFT 1559 11:59:00 11.59 08/02/2013 15:21:00 MSFT 1521 15:20:00 15.2 08/01/2013 10:06:00 YHOO 1006 10:05:00 10.05 08/01/2013 14:21:00 YHOO 1421 10:05:00 10.05 08/01/2013 15:58:00 YHOO 1558 10:05:00 10.05 08/02/2013 15:27:00 YHOO 1527 15:25:00 15.25 ... View more

Difficult merge/join with huge datasets

by Fluorite | Level 6 in SAS Programming
‎08-29-2014 01:44 PM
‎08-29-2014 01:44 PM
I'm good with sql, but it's not the right tool for this job.  I have to connect execution quantities for stocks with their last known prices. (That is, I can partially join the tables on date and ticker, but I have to find the price from Prices that is the latest time before the Event's time.)   Below is some sample code and a sql statement that works, but is too inefficent my large datasets.  I'm wondering if there is some tricky data step with merging that can do this better. data Events;     format TradeDate mmddyys10. TradeTime time.;     input TradeDate :anydtdte. TradeTime :anydttme. Ticker :$4.  Qty :best10.; cards; 1Aug2013 10:00 MSFT 1000 1Aug2013 10:02 MSFT 1002 1Aug2013 14:20 MSFT 1420 1Aug2013 15:59 MSFT 1559 2Aug2013 09:31 MSFT 0931 2Aug2013 12:01 MSFT 1201 2Aug2013 15:20 MSFT 1520 2Aug2013 15:21 MSFT 1521 1Aug2013 10:00 YHOO 1000 1Aug2013 10:06 YHOO 1006 1Aug2013 14:21 YHOO 1421 1Aug2013 15:58 YHOO 1558 2Aug2013 09:30 YHOO 0930 2Aug2013 12:01 YHOO 1201 2Aug2013 15:20 YHOO 1520 2Aug2013 15:27 YHOO 1527 1Aug2013 10:00 AAPL 1000 1Aug2013 10:06 AAPL 1006 1Aug2013 14:21 AAPL 1421 1Aug2013 15:58 AAPL 1558 2Aug2013 09:30 AAPL 0930 2Aug2013 12:01 AAPL 1201 2Aug2013 15:20 AAPL 1520 2Aug2013 15:27 AAPL 1527 ;     run; proc sort data=Events;     by Ticker TradeDate TradeTime; data Prices;     format TradeDate mmddyys10. TradeTime time.;     input TradeDate :anydtdte. TradeTime :anydttme. Ticker :$4.  Price :best10.; cards; 1Aug2013 09:30 MSFT 09.30 1Aug2013 10:01 MSFT 10.01 1Aug2013 10:02 MSFT 10.02 1Aug2013 11:59 MSFT 11.59 2Aug2013 09:30 MSFT 09.30 2Aug2013 10:01 MSFT 10.01 2Aug2013 15:18 MSFT 15.18 2Aug2013 15:19 MSFT 15.19 2Aug2013 15:20 MSFT 15.20 2Aug2013 15:21 MSFT 15.21 1Aug2013 10:01 YHOO 10.01 1Aug2013 10:05 YHOO 10.05 2Aug2013 09:28 YHOO 09.28 2Aug2013 09:29 YHOO 09.29 2Aug2013 11:00 YHOO 11.00 2Aug2013 15:25 YHOO 15.25 2Aug2013 16:00 YHOO 16.00 ;     run; proc sort data=Prices;     by Ticker TradeDate TradeTime; proc print data=Events; proc print data=Prices; /* too slow on big data */ proc sql;     select a.*, b.TradeTime as PriceTime, b.Price     from Events a     join Prices b        on a.TradeDate = b.TradeDate       and a.Ticker = b.Ticker       and b.TradeTime < a.TradeTime     group by a.TradeDate, a.Ticker, a.TradeTime     having b.TradeTime = max( b.TradeTime)     order by a.Ticker, a.TradeDate, a.TradeTime; Here are the results: TradeDate TradeTime Ticker Qty PriceTime Price 08/01/2013 10:00:00 MSFT 1000 9:30:00 9.3 08/01/2013 10:02:00 MSFT 1002 10:01:00 10.01 08/01/2013 14:20:00 MSFT 1420 11:59:00 11.59 08/01/2013 15:59:00 MSFT 1559 11:59:00 11.59 08/02/2013 9:31:00 MSFT 931 9:30:00 9.3 08/02/2013 12:01:00 MSFT 1201 10:01:00 10.01 08/02/2013 15:20:00 MSFT 1520 15:19:00 15.19 08/02/2013 15:21:00 MSFT 1521 15:20:00 15.2 08/01/2013 10:06:00 YHOO 1006 10:05:00 10.05 08/01/2013 14:21:00 YHOO 1421 10:05:00 10.05 08/01/2013 15:58:00 YHOO 1558 10:05:00 10.05 08/02/2013 9:30:00 YHOO 930 9:29:00 9.29 08/02/2013 12:01:00 YHOO 1201 11:00:00 11 08/02/2013 15:20:00 YHOO 1520 11:00:00 11 08/02/2013 15:27:00 YHOO 1527 15:25:00 15.25 ... View more

Re: Is it safe to sql join on datetimes?

by Fluorite | Level 6 in SAS Programming
‎08-08-2014 03:50 PM
‎08-08-2014 03:50 PM
I used your technique for generating differences, and tested sql joins.  With datetimes I get exact matches up to 1e-6 (milliseconds); once the differences get smaller than that sql "mistakenly" joins values that it should not.  (That is, it agrees with your statement "SAS won't even consider a deff of 10-7 to be a difference".) With times (no date part), I get accurate joining down to 1e-11.  To play it safe, I won't go beyond nonoseconds. ... View more

Re: Is it safe to sql join on datetimes?

by Fluorite | Level 6 in SAS Programming
‎08-08-2014 03:04 PM
‎08-08-2014 03:04 PM
I try not to be a one-trick-pony, but I do like sql and make good use of it in SAS.  If is correct (and I plan to do some join tests) and sql joining will work with microsecond times, then I can stick to what I know well.  If not, then it will be time for a new tool in the toolbox. ... View more

Re: Is it safe to sql join on datetimes?

by Fluorite | Level 6 in SAS Programming
‎08-08-2014 03:01 PM
‎08-08-2014 03:01 PM
I generally use where abs(a.dt-b.dt) < 0.0001 ... View more

Is it safe to sql join on datetimes?

by Fluorite | Level 6 in SAS Programming
‎08-08-2014 01:52 PM
‎08-08-2014 01:52 PM
Joining on floating point numbers is a bad idea, yet SAS does not offer an integer type.  What precision datetime (milliseconds?  microseconds?) can I safely do an equi-join on (or test for equality)?  I realize that with a time field, I should get higher precision. I'm thinking that because the FUZZ functions define fuzz as 1e-12, I should be safe going down to nanoseconds (1e-9) with a time field, and milliseconds (1e-3, maybe even microseconds?) with a datetime field.  Can anyone point me to a definitive reference?  Google hasn't turned up anything fo me. ... View more

Re: CSV file contains embedded CR and LF inside double quotes. How to ...

by Fluorite | Level 6 in SAS Data Management
‎07-25-2014 01:44 PM
‎07-25-2014 01:44 PM
Tom, You've replied about termstr=CRLF many times.  I know, becuase I just searched for "embedded linefeed" (a problem I needed help with) and found them.  It solved my problem! ... View more

Re: Data Step / Arrays help needed !!!

by Fluorite | Level 6 in SAS Programming
‎07-25-2014 09:29 AM
‎07-25-2014 09:29 AM
For the record: I'm impressed with the quality of the help you have received.  Props to , @data_null_; , . ... View more

Re: PROC SQL -increment counting

by Fluorite | Level 6 in SAS Programming
‎07-23-2014 08:24 AM
‎07-23-2014 08:24 AM
>>> it should be supported by one... of your fields. I have used monotonic() to create the field. Would you say that if I only use monotonic() when I also use an ORDER BY clause that I'm safe and within the bounds of relational theory? ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎09-01-2015 07:11 AM
Likes given to
Likes from
Quick links