Hi Guys, I am presently working with a matrix table containing a lo-date a lo-amount and an interest rate.  There are 4,000,000 observations within this table.  I need to perform a look up on the matrix table based on the effective date of the transaction and the current balance in my transaction data.  The original code uses SQL to perform this look up and although it works it takes over 2 hours to complete.  Can someone offer some insight into a more effecient method obtaining the desired outcome?  I have added indexes and we are seeing a small amount of improvement, but no where near what is required. The logic is if Lo_Date <= Effective_Date and Lo_Amount <= Balance then Int_Rate Thank you very much for your help. Matrix_Table Lo_Date Lo_Amount Int_Rate 1/01/2014 0 0% 1/01/2014 1000 5% 1/01/2014 10000 10% 1/01/2014 100000 15% 1/02/2014 0 5% 1/02/2014 1000 10% 1/02/2014 10000 15% 1/02/2014 100000 20% Transaction_Table Effective_Date Balance 15/01/2014 11000 15/01/2014 1000 15/01/2014 15000 15/01/2014 110000 15/01/2014 900 15/01/2014 100 15/01/2014 6000 15/01/2014 100001 15/01/2014 99999 15/02/2014 11000 15/02/2014 1000 15/02/2014 15000 15/02/2014 110000 15/02/2014 900 15/02/2014 100 15/02/2014 6000 15/02/2014 100001 15/02/2014 99999 Result_Table Effective_Date Balance Int_Rate 15/01/2014 11000 10% 15/01/2014 1000 5% 15/01/2014 15000 10% 15/01/2014 110000 15% 15/01/2014 900 0% 15/01/2014 100 0% 15/01/2014 6000 5% 15/01/2014 100001 15% 15/01/2014 99999 10% 15/02/2014 11000 15% 15/02/2014 1000 10% 15/02/2014 15000 15% 15/02/2014 110000 20% 15/02/2014 900 5% 15/02/2014 100 5% 15/02/2014 6000 10% 15/02/2014 100001 20% 15/02/2014 99999 15% ... View more

Hi Guys, Don't judge me, but I don't use SQL very often.  I am sure this is very simple but all I want to do is find the maximum of all conditions in a HAVING clause.  So for example if I am using the the SASHELP.CLASS dataset (I am only using this because it highlights what I am attempting to do) I would like to know which student is both the heaviest and the oldest for each sex.  If they aren't the the oldest AND heaviest I want them excluded. I thought that the max function and grouping would do this but I failed. PROC SQL; CREATE TABLE HAVE AS SELECT * FROM SASHELP.CLASS GROUP BY SEX HAVING AGE = MAX(AGE) AND WEIGHT = MAX(WEIGHT) ; QUIT; All I want is to output Philip as he is both the oldest and the heaviest. I know other ways to do this, but the code is already in SQL and it has to stay that way. Can someone please help me out? Thanks in advance. ... View more