No, we always want the previous matching record valid date, unless the immediate post-matching record's valid date is same as current record valid date.   In this case of 3rd record, since its pre-matching record date is 01Jan2013 and post  matching record's date is 01Oct2014, we should take the minimum of them i.e. 01Jan2013 and its corresponding CUST_SK i.e. 12559928. If the date of 3rd record had been 01Oct2014 (instead of 01Aug20014) then it is same as in the next matching record (4th) in sequence. In such case 3rd record should get date=01Oct2014 and SK=72971041.   Expected results highlighted in green:   41 41 12559928 01Jan2013 12559928 01Jan2013    =>    match=y, prevmatchdate=01Jan2013, postmatchdate=01Oct2014 41 4615581 14452649 01Feb2013 12559928 01Jan2013 => match=n, prevmatchdate=01Jan2013, postmatchdate=01Oct2014 41 4615581 67316104 01Aug2014 12559928 01Jan2013 => match=n, prevmatchdate=01Jan2013, postmatchdate=01Oct2014 41 41 72971041 01Oct2014 72971041 01Oct2014 => match=y, prevmatchdate=01Oct2014, postmatchdate=blank ... View more

Hi,   Thanks for the help. But I see one issue in the results. Please refer below. The record highlighted below in red should have the CUST_SK= 12559928, VALID_DTTM=01Jan2013. Not sure how to correct that issue from your code. Can you suggest? Except that everything else seems fine.   CURR_RK ORIG_RK CUST_SK VALID_DTTM want_sk want_dt 41 2673 2342321 01Sep2012 12559928 01Jan2013 41 41 12559928 01Jan2013 12559928 01Jan2013 41 4615581 14452649 01Feb2013 12559928 01Jan2013 41 4615581 67316104 01Aug2014 72971041 01Oct2014 41 41 72971041 01Oct2014 72971041 01Oct2014 41 4615581 74341618 01Nov2014 72971041 01Oct2014 41 8828883 63829111 01Jul2015 72971041 01Oct2014 9217 9217 21128775 01Jan2013 21128775 01Jan2013 9217 10124544 15142897 01Jan2013 21128775 01Jan2013 9217 9217 30874351 01Mar2013 30874351 01Mar2013 9217 9217 71276102 01Sep2014 71276102 01Sep2014 9217 10124544 74885982 01Nov2014 71276102 01Sep2014 346372 346372 6478392 01Sep2014 6478392 01Sep2014 7801669 6368875 2591876 01Dec2012 3829995 01Jan2013 7801669 7801669 3829995 01Jan2013 3829995 01Jan2013 7801669 7801669 97082679 01Aug2015 97082679 01Aug2015 7801669 6368875 106930088 01Nov2015 107207912 01Nov2015 7801669 7801669 107207912 01Nov2015 107207912 01Nov2015 ... View more

  Hi,   That is the reason for me trying to create date range variables "max_before_valid_dttm" & "min_after_valid_dttm" for each record based on its pre-match and post-match valid_dttm values, we will consider the min value of them as final_dttm.   Apart from that, the code provided now is not as expected and causing some wrong cust_sk and date assignments.   The code given yesterday was perfectly working on subset data. But failing due to HASH memory issue in SAS when run on full table. If we can resolve that, it will be absolutely fine.   Working SAS Code with HASH object program:   data one; infile datalines; input CURR_RK ORIG_RK CUST_SK VALID_DTTM: anydtdte10. ; format VALID_DTTM date9.; datalines; 41 2673 2342321 1-Sep-12 41 41 12559928 1-Jan-13 41 4615581 14452649 1-Feb-13 41 4615581 67316104 1-Aug-14 41 41 72971041 1-Oct-14 41 4615581 74341618 1-Nov-14 41 8828883 63829111 1-Jul-15 9217 9217 21128775 1-Jan-13 9217 10124544 15142897 1-Jan-13 9217 9217 30874351 1-Mar-13 9217 9217 71276102 1-Sep-14 9217 10124544 74885982 1-Nov-14 346372 346372 6478392 1-Sep-14 7801669 6368875 2591876 1-Dec-12 7801669 7801669 3829995 1-Jan-13 7801669 7801669 97082679 1-Aug-15 7801669 6368875 106930088 1-Oct-15 7801669 7801669 107207912 1-Nov-15 ; run; data one; set one; by curr_rk; if first.curr_rk then n=0; n+1; run; data want; if _n_=1 then do;   if 0 then set one(rename=(CUST_SK=new_sk VALID_DTTM=new_dt));   declare hash h1(dataset:'one(rename=(CUST_SK=new_sk VALID_DTTM=new_dt)    where=(CURR_RK=ORIG_RK))');   h1.definekey('CURR_RK','ORIG_RK');   h1.definedata('new_sk','new_dt');   h1.definedone();     if 0 then set one(keep=ORIG_RK rename=(ORIG_RK=_ORIG_RK));   declare hash h2(dataset:'one(rename=(ORIG_RK=_ORIG_RK    CUST_SK=new_sk VALID_DTTM=new_dt))');   h2.definekey('CURR_RK','n');   h2.definedata('new_sk','new_dt','_ORIG_RK');   h2.definedone(); end; set one; by curr_rk; if first.curr_rk then do; if curr_rk = orig_rk then do;new_sk=CUST_SK; new_dt=VALID_DTTM;end;   else do;call missing(new_sk,new_dt);rc=h1.find(key:CURR_RK,key:​CURR_RK);end; end; else do; if curr_rk = orig_rk then do;new_sk=CUST_SK; new_dt=VALID_DTTM;end;   else do;    do k=n-1 to 1 by -1;     call missing(new_sk,new_dt);     rc=h2.find(key:CURR_RK,key:k);     if CURR_RK=_ORIG_RK then leave;    end;   end; end; drop rc k n _ORIG_RK; run;     ... View more

Hi,   If this is the data: 7801669 7801669 97082679 1-Nov-15 7801669 6368875 106930088 2-Nov-15 7801669 7801669 107207912 3-Nov-15   Since the 2nd record has one pre-match and one post-match consecutively, the minimum value of both their dates will be taken i.e. in this case 1-Nov-2015.   Expected Result would be: 7801669 7801669 97082679 1-Nov-15 7801669 6368875 97082679 1-Nov-15 7801669 7801669 107207912 3-Nov-15   ... View more

Hi,   In such case, I should pick the 1st record values and place it into the 2nd record.   7801669 7801669 97082679 1-Nov-15  7801669 6368875 106930088 1-Nov-15 7801669 7801669 107207912 1-Nov-15   New data would be: 7801669 7801669 97082679 1-Nov-15 7801669 6368875 97082679 1-Nov-15 7801669 7801669 107207912 1-Nov-15   Anyway, the previous program you have sent is working fine now as I sorted the data with matching flag now. The new problem I am facing is the HASH object execution failure due to insufficient memory in Work. Can I push this entire code onto Oracle database processing? Will Hash programs work on Oracle using SQL pass through? ... View more

Hi Ksharp,   I tried to execute the sas code you have given (using Hash object) through DI Studio on our UAT environment but it is failing: ERROR:  Hash object added 85983216 items when memory failure occured 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.   My Input table is having 112937794 observations and hence HASH object process is causing the failure. I verified my "xmrlmem" option and it is currently set to 6442450944.   I am not getting any other option handle this issue. Do you have any suggestions?   ... View more

Hi Ksharp,   Thank you very much for the solution logic provided. I am able to create new_sk & new_dttm with this logic but we found a small here is a small challenge to it. For e.g. if you observe last 3 records in below table, the 3rd record which is a matching record should get as is new_sk & new_dttm. Where as since 4th record is a non-match record, we need to verify its VALID_DTTM against pre-match record and also with post-match record values. In this case, the 5th record VALID_DTTM matches to the 4th record VALID_DTTM and hence we need to get its CUST_SK, VALID_DTTM and replace the values in the 4th record. Else it should pick the values from 3rd record (pre-matching record) irrespective of its VALID_DTTM values.   Because of this complexity, we want to create two seperate date variables based on pre-match VALID_DTTM value and post-match VALID_DTTM value. Then take the min of them as FINAL_DTTM. Accordingly replace the CUST_SK values for each record. They also want to use these dates in further jobs to implement some logic.     CURR_RK ORIG_RK CUST_SK VALID_DTTM VALID_END_DTTM FLAG MAX_DT_BFOR_VALID_DTTM MIN_DT_AFTR_VALID_DTTM EXPECTED FINAL VALID_DTTM EXPECTED FINAL CUST_SK 7801669 6368875 2591876 1-Dec-12 31-Oct-15 FALSE - 1-Jan-13 1-Jan-13 3829995 7801669 7801669 3829995 1-Jan-13 31-Jul-15 TRUE 1-Jan-13 1-Aug-15 1-Jan-13 3829995 7801669 7801669 97082679 1-Aug-15 31-Oct-15 TRUE 1-Aug-15 1-Nov-15 1-Aug-15 97082679 7801669 6368875 106930088 1-Nov-15 31-Dec-47 FALSE 1-Nov-15 1-Nov-15 1-Nov-15 107207912 7801669 7801669 107207912 1-Nov-15 31-Dec-47 TRUE 1-Nov-15 - 1-Nov-15 107207912     Hope I am able to explain well. Please help if you have any alternative solution for this. Thank you in advance.       ... View more

Hi RW9,   Yes, you got the question right. I want to merge those 3 records (pre-match, current, post-match) and create two new date variables MAX_DT_BFOR_VALID_DTTM & MIN_DT_AFTR_VALID_DTTM  as shown below. I will then use these two date variables to do the further logic.   Desired Output for me:  CURR_RK ORIG_RK CUST_SK VALID_DTTM FLAG MAX_DT_BFOR_VALID_DTTM MIN_DT_AFTR_VALID_DTTM EXPECTED FINAL VALID_DTTM EXPECTED FINAL CUST_SK 41 2673 2342321 1-Sep-12 FALSE - 1-Jan-13 1-Jan-13 12559928 41 41 12559928 1-Jan-13 TRUE 1-Jan-13 1-Oct-14 1-Jan-13 12559928 41 4615581 14452649 1-Feb-13 FALSE 1-Jan-13 1-Oct-14 1-Jan-13 12559928 41 4615581 67316104 1-Aug-14 FALSE 1-Jan-13 1-Oct-14 1-Jan-13 12559928 41 41 72971041 1-Oct-14 TRUE 1-Oct-14 - 1-Oct-14 72971041 41 4615581 74341618 1-Nov-14 FALSE 1-Oct-14 - 1-Oct-14 72971041 41 8828883 63829111 1-Jul-15 FALSE 1-Oct-14 - 1-Oct-14 72971041 9217 9217 21128775 1-Jan-13 TRUE 1-Jan-13 1-Mar-13 1-Jan-13 21128775 9217 10124544 15142897 1-Jan-13 FALSE 1-Jan-13 1-Mar-13 1-Jan-13 21128775 9217 9217 30874351 1-Mar-13 TRUE 1-Mar-13 1-Sep-14 1-Mar-13 30874351 9217 9217 71276102 1-Sep-14 TRUE 1-Sep-14 1-Nov-14 1-Sep-14 71276102 9217 10124544 74885982 1-Nov-14 FALSE 1-Sep-14 - 1-Sep-14 71276102 346372 346372 6478392 1-Sep-14 TRUE 1-Sep-14 1-Sep-14 1-Sep-14 6478392 7801669 6368875 2591876 1-Dec-12 FALSE - 1-Jan-13 1-Jan-13 3829995 7801669 7801669 3829995 1-Jan-13 TRUE 1-Jan-13 1-Aug-15 1-Jan-13 3829995 7801669 7801669 97082679 1-Aug-15 TRUE 1-Aug-15 1-Nov-15 1-Aug-15 97082679 7801669 6368875 106930088 1-Nov-15 FALSE 1-Nov-15 1-Nov-15 1-Nov-15 107207912 7801669 7801669 107207912 1-Nov-15 TRUE 1-Nov-15 - 1-Nov-15 107207912     Thanks for the logic provided. I tried it in SAS but it's throwing CORRELATION errors.   proc sql; create table WANT as select A.*, B.VALID_DTTM as PREV_DTTM, C.VALID_DTTM as NEXT_DTTM from ONE A left join (select * from ONE group by CURR_RK having CURR_RK=A.CURR_RK and VALID_DTTM < A.VALID_DTTM and VALID_DTTM=max(VALID_DTTM)) B on A.CURR_RK=B.CURR_RK left join (select * from ONE group by CURR_RK having CURR_RK=A.CURR_RK and VALID_DTTM > A.VALID_DTTM and VALID_DTTM=min(VALID_DTTM)) C on A.CURR_RK=C.CURR_RK; quit;   SQL error:   ERROR: Correlated reference to column CURR_RK is not contained within a subquery. ERROR: Correlated reference to column VALID_DTTM is not contained within a subquery. ERROR: Correlated reference to column CURR_RK is not contained within a subquery. ERROR: Correlated reference to column VALID_DTTM is not contained within a subquery.   The query seems to be correct but I am not sure what is causing the issue.  I need to investigate and see if it gives me the desired new date variables.   Thanks once again. ... View more

Hi,   Thanks for the quick response. I need both. Some times I need previous record values and some times I need later record values depending on the conditions.   Do you mean, first fetch from the previous records into a dataset and then sort it again in reverse order to fetch values from later records? Will it work? ... View more

Hi,   Thanks for the response and inputs given. Since my graph has to use grouping and stacking, I opted for Proc Gchart which has relatively limited options in certain needs. And that's the reason I am using same Proc Gchart to display its corresponding legends separately.    Regards ravib ... View more

Hi ballardw,   Thanks for the response and sorry if my previous explanation was not clear. You almost got the issue and thanks for your suggestions on creating HSIZE, VSIZE macro variables. I did the same way using 5 different number ranges for legend entries as an immediate solution. But since my input data has dynamic legend, I want to adjust its display area also dynamically instead of hard coding the graph sizes. So in this approach, I had to display only the legend separately if the number is exceeding 20 to avoid warnings. I have added few more details in the attached document. Hope you get more clarity with my quesiton.   Please review and let me know if there is any better way to approach? Thanks once again.     Regards ravib  ... View more

Hi,   Thanks for the solution and sorry for the late response due to work load. My REF values need to be dynamically calculated from the input values. So I have created macro variables to calculate and call them in REF= option in Proc Gchart, which has worked well. Thanks once again.     Best Regards RaviB ... View more