Thanks PG. Its been running for over almost two hours but I am hoping it will be successful. ... View more

HI PG- I used your syntax and I am not getting the results I am expecting. A no match resulted when a match should have been produced. Here is the code: PROC SQL; CREATE TABLE NEW AS SELECT E.*, P.IDKEY, P.CERTKEY, P.BIRDATE, P.GNDRCODEKEY, P.SAS_START_DATE, P.SAS_END_DATE, FROM ENR AS E LEFT JOIN PGM AS P ON E.IDKEY=P.IDKEY and p.SAS_START_DATE LE e.SAS_WITHDRLDATE_DATE and p.SAS_END_DATE GE e.SAS_ENROLLDATE_DATE group by e.SAS_ENROLLDATE_DATE, e.SAS_WITHDRLDATE_DATE having p.SAS_START_DATE=max(p.SAS_START_DATE) or p.SAS_START_DATE is missing; select * from new; quit; However, when I manually entered the records from the two table(expecting a match) into the code below- it resulted in a match. data enr; input idkey enr_start enr_end; datalines; 50 19946 20243 ; RUN; data pro; input idkey pgm_start pgm_end ; datalines; 50 19946 9999999 50 19946 9999999 50 20237 9999999 ; PROC SQL; CREATE TABLE NEW AS SELECT e.*, p.pgm_start, pgm_end FROM ENR AS E LEFT JOIN PRO AS P ON E.IDKEY=P.IDKEY and p.pgm_start <=e.enr_end and p.pgm_end >= e.enr_start group by e.enr_start, e.enr_end having p.pgm_start=max(p.pgm_start) or p.pgm_start is missing; select * from new; quit; Do you know what could be wrong?   Also- the join didnt return all records from my original table.  Dataset 1 contains 114,000 records (which is the SOURCE) Dataset 2 contains 354,000 and join only netted 33,000 records?  ... View more

Hi PG- Can this code work if I used SAS dates instead of pgm_start pgm_end enr_start enr_end? I tried but I couldn't get it to work- but it did work when working with actual dates mmddyy10. Also, do you know if the SQL code can produce an output dataset of non-matches? Or how would I go about searching for those ENR records that had zero matches in the PGM dataset? ... View more

Hi PG-  Unfortunately it didn't work.  I was expecting one match for this particular IDKEY and the updated code resulted in zero matches whereas, your original code resulted in three matches.    Here is an example with data.  All three of the records in the PGM dataset would qualify to join to the one ENR data record.  But the ENR data record I am trying to match to would be the last recrod in the PGM dataset. (the one with CERTKEY=1020)  Would you be able to incorporate the max(certkey) into the syntax to help select the most recent/decending?? (In my PGM table, the certkey is unique and the number increases as new records are added) See below:   Thank you so much!   ENR DATASET       IDKEY ENR_START ENR_END DATA 50 AUG 11 2014 JUNE 4 2015 BSIELD         PGM DATASET       IDKEY PGM_START PGM_END CERTKEY 50 AUG 11/2014 999999 836 50 AUG 11/2014 999999 820 50 MAY 29 2015 999999 1020 ... View more

Hi PG- Thanks so much for looking at my data problem. This semi-worked. The issue is that there could be multiple rows of PGM records and the ENR record is being multiplied by the number of PGM records that qualify. In these instances, is it possible to set up a 'tie breaker'? Meaning have the ENR record only join to the most recent (descending) date of the PGM record? Thank you so much! ... View more

Thank you.  I removed nodupkey and ran separately and it seemed to have worked.  ... View more

Yes.  There was a 'BY'  it was a copy and paste error.  Also the variables in each dataset should be unique so there should not be any over-written data.  Thank you ... View more

Thank you! I was wondering if this can be accomplished using the Data Step and using a merge like below- where I can control the counts of the various merges?   DATA YFYS YFNS NFYS; MERGE table1(in=a) table2_date1(in=b); by id ; IF A AND B THEN OUTPUT YFYS; IF A=1 AND B=0 THEN OUTPUT YFNS; IF A=0 AND B=1 THEN OUTPUT NFYS; RUN;  ... View more

Hi Astounding! 1) I am able to convert the start and end dates to SAS dates if that will make the programming easier. 2) Yes- I would want the two different locations should the dates from Table 1 overlap Table 2 Thank U ... View more