What will be the first code set at the first entry for a given id, depending on flag? What is the formula for the next entry encountered? ... View more

@MV04 , did you find your answer 🙂 ... View more

Within proc sql, use connect to connect to the DBMS, execute to run the command there, and disconnect to terminate the connection. ... View more

Thanks so much Yabwon . That did work. ... View more

Hi @MV04    In your results, shouldn't  123 5.62 01-Jun-09 be 4.88? as table1 effectivedate look up is 123 4.88 01-Jun-09     So if the above understanding and assumption of mine is correct, the following should work   data table1; format Effective_date date9.; infile cards dlm= "," dsd; input id rate Effective_date ddmmyy10.; cards ; 123,5.62,18/05/2009 123,4.88,01/06/2009 123,4.25,01/09/2009 123,4.12,01/12/2009 123,4.25,01/03/2010 123,4.37,01/03/2011 123,4.5 ,01/09/2011 123,4.62,01/12/2011 123,4.5 ,01/06/2012 123,4.25,03/09/2012 123,4.12,03/12/2012 ; run; data table2; infile cards dsd ; input Formmated_Date :ddmmyy10. ID Close_dt :ddmmyy10. Open_dt : ddmmyy10.; format Formmated_Date ddmmyy10. id 3. Close_dt ddmmyy10. Open_dt ddmmyy10.; cards ; 18/05/2009,123,30/01/2015,18/05/2009 19/05/2009,123,30/01/2015,18/05/2009 20/05/2009,123,30/01/2015,18/05/2009 21/05/2009,123,30/01/2015,18/05/2009 22/05/2009,123,30/01/2015,18/05/2009 23/05/2009,123,30/01/2015,18/05/2009 24/05/2009,123,30/01/2015,18/05/2009 25/05/2009,123,30/01/2015,18/05/2009 26/05/2009,123,30/01/2015,18/05/2009 27/05/2009,123,30/01/2015,18/05/2009 28/05/2009,123,30/01/2015,18/05/2009 29/05/2009,123,30/01/2015,18/05/2009 30/05/2009,123,30/01/2015,18/05/2009 31/05/2009,123,30/01/2015,18/05/2009 01/06/2009,123,30/01/2015,18/05/2009 02/06/2009,123,30/01/2015,18/05/2009 03/06/2009,123,30/01/2015,18/05/2009 04/06/2009,123,30/01/2015,18/05/2009 05/06/2009,123,30/01/2015,18/05/2009 06/06/2009,123,30/01/2015,18/05/2009 07/06/2009,123,30/01/2015,18/05/2009 08/06/2009,123,30/01/2015,18/05/2009 09/06/2009,123,30/01/2015,18/05/2009 10/06/2009,123,30/01/2015,18/05/2009 11/06/2009,123,30/01/2015,18/05/2009 12/06/2009,123,30/01/2015,18/05/2009 13/06/2009,123,30/01/2015,18/05/2009 14/06/2009,123,30/01/2015,18/05/2009 15/06/2009,123,30/01/2015,18/05/2009 16/06/2009,123,30/01/2015,18/05/2009 17/06/2009,123,30/01/2015,18/05/2009 18/06/2009,123,30/01/2015,18/05/2009 19/06/2009,123,30/01/2015,18/05/2009 20/06/2009,123,30/01/2015,18/05/2009 21/06/2009,123,30/01/2015,18/05/2009 22/06/2009,123,30/01/2015,18/05/2009 ; run; data want ; if _n_=1 then do; if 0 then set table1; declare hash H (dataset:'table1') ; h.definekey ("id","Effective_date") ; h.definedata ("rate") ; h.definedone () ; end; set table2; rc=h.find(key:id,key:Formmated_Date); keep ID Rate Formmated_Date; run; ... View more

The output dataset should be something like below id1 open_date amt_d1 amt_d2 123567 01-Apr-09 5 6 123567 10-Apr-09 4 7 123567 01-May-09 2 4 123567 01-Jun-10 5 1 123567 01-Jul-10 1 5 123567 01-Sep-10 2 3 123567 01-Oct-10 6 8 123567 03-Dec-12 4 2 123568 02-Sep-11 6 5 123568 02-Apr-09 5 3 123568 12-Apr-09 4 5 123568 11-May-09 2 7   so amt is the last amt prior to each date as you loop through the dates in between d1 and d2 so if we loop through open date in d1 then the dtes will be like 01-apr,02-apr,03-apr,04-apr,05-apr,06-apr and so on and these dates we need to compare with the open date from d2 so if the date say 01apr2009 from d1 falls between the dates 02ma2009 till 11apr2009 then return the amt at 02mar2009,if its not falling between those dates then check the next two dates (make sure the open dte in d2 is sorted in ascending order)   ... View more