Find a 24 hour moving average of transactions by account - Attempting with Hash Tables

[mkeintz]

@DonH :  I agree.  The only reason I would recommend hash-of-hashes would be if the data were sorted by timestamp/customerid.  Otherwise it's wasteful.

However, even in that scenario the OP wouldn't need to keep all the records for all the customers in the hash objects - just the last 24 hours for each customer.  Of course, that memory savings would be at the expense of adding code to remove data items from a hash as they become stale.

The OP's task is logically equivalent to maintaining a portfolio of stocks with irregularly implemented BUYs and SELLs of one or another constituent of the portfolio, which is the context in which I wrote From Stocks to Flows: Using SAS® Hash Objects for FIFO, LIFO, and other FO’s.  In particular, the OP's task is like a FIFO treatment of inventory.

[Array_Mover]

Thank you for responding.  I queried the transactions table and found, for the month of August, there are over 2 million distinct accounts with over 60 million transactions.

If that is too much, I would be more than happy doing a subset, a weeks worth, or only certain types of accounts. I work in financial services and we have a fraud mitigation tool that uses these moving averages (24 hours, 3 days, 7 days, 30 days) but the data is 'live' and constantly updates as customers transact, so it isn't available to us to use for ex post insights into how well rules are performing.

That being said, I would like to replicate this functionality in SAS so I can run simulations of rules over past data to see the rule performance based on different adjustments or scenarios.