Thanks again Tom, this worked perfectly!   Finally got it to work by joining the claims data with the vendor data by MBR_ID, DOS & NPI to give me the records I needed then, I split this joined file into two tables, one with vendor info and one with our claims info.  then I could use the match merge to get the output I was looking for.   Here is a look at the code- FYI, not all of this will be used in the final process, it was used here to validate my output: %MACRO UTL_LIBS(); %INCLUDE '/source/riskadj/informatics/LIBNAMES/DECLARE_LIBRARIES.sas'; %MEND UTL_LIBS; %UTL_LIBS(); RUN; LIBNAME Vineeth '/data152/riskadj2/vin'; LIBNAME model_18 '/data152/riskadj2/vin/2018Model'; libname Merx '/data152/riskadj2/HH/medicarerx'; LIBNAME Medical '/data150/rari/Medical'; LIBNAME Hao '/data156/mgm_cra/mgm_test/'; LIBNAME Member '/data143/mcrf_ap2/prod/membership/input'; LIBNAME R2016_2 '/data152/riskadj2/informatics/retrospective/201611'; LIBNAME R2019_2 '/data152/riskadj2/informatics/retrospective/201912'; LIBNAME R2020 '/data152/riskadj2/informatics/retrospective/202007'; LIBNAME CLM2020 '/data166/riskadj/DW_Register/_202106'; LIBNAME MRA20202 '/data156/mgm_mra/Retro2020_2'; LIBNAME MRA2020 '/data156/mgm_mra/Retro2020'; LIBNAME ATHENA '/data198/crmo_app/prod/athena/load'; LIBNAME AS '/data198/crmo_app/prod/allscripts/load'; LIBNAME ECW '/data198/crmo_app/prod/ecw/load'; LIBNAME MOXE '/data198/crmo_app/prod/moxe/load'; LIBNAME NEXTGEN '/data198/crmo_app/prod/nextgen/load'; LIBNAME KS '/data152/riskadj2/informatics/ks'; LIBNAME R2021 '/data152/riskadj2/informatics/retrospective/202108'; LIBNAME MDCR_III '/data156/mgm/MEDICARE_INPUT'; lIBNAME MRA2021 '/data156/mgm_mra/Retro2021'; LIBNAME SVP '/data152/riskadj2/informatics/SVP'; libname ATRAC '/data152/riskadj2/informatics/ATRAC'; libname R2020_2 '/data152/riskadj2/informatics/retrospective/202012'; libname ATRAC '/data152/riskadj2/informatics/ATRAC'; libname R2020_2 '/data152/riskadj2/informatics/retrospective/202012'; /* 1. Get ATRAC file & rename variables to match claims file */ PROC SQL; CREATE TABLE CCR2021A as SELECT distinct ID, MemberID, MemberIndividualID as ENRLE_ID, ProviderType, DateOfServiceFrom as STMT_STRT_DT, DateOfServiceTo as STMT_STOP_DT, RenNPI as SVCG_PVDR_NPI_NBR, ICDDisposition, RenTIN as SVCG_PVDR_TIN_NBR, RenPIN as SVCG_PVDR_PIN_TXT, ChaseID, ClaimID, ICDCODE as CDF_DX FROM atrac.TBLIMPORTEDRAPSDATA2020(obs=10000); QUIT; /* 2. Get claim data & join with dx data */ /* MUST BE A LEFT JOIN */ /* Left join = everything from Claims_raw & only what matches from Claims_dx*/ /* Inner join = ONLY what matches from both sets */ PROC SQL; CREATE TABLE CLAIMSA AS SELECT DISTINCT a.CLM_ID, a.SRC_CLM_ID, a.STMT_STRT_DT, a.STMT_STOP_DT, a.ENRLE_ID, a.SVCG_PVDR_NPI_NBR, a.SVCG_PVDR_PIN_TXT, a.SVCG_PVDR_TIN_NBR, b.CLM_ID, b.ICD9_DX_CD as CLM_DX FROM R2020_2.CLAIMS_RAW a LEFT JOIN R2020_2.RRODS_CLAIM_DX b ON ICD9_DX_CD=compress(ICD9_DX_CD,".") AND a.CLM_ID = b.CLM_ID ; QUIT; /* THIS TABLE JOINS CLAIMS AND ATRAC REGARDLESS OF DX */ /* SINCE WE ONLY WANT INFORMATION ON CLAIMS THAT WERE SENT TO THE VENDOR */ /* AND MATCH OUR VENDOR FILE VIA MBR ID, DOS & NPI, */ /* IF YOU DO AN INNER JOIN, WE ONLY GET WHAT MATCHED IN BOTH */ /* A LEFT JOIN WILL GIVE ME EVERYTHING FROM THE VENDOR FILE AND ONLY */ /* WHAT MATCHES IN CLAIMS-THIS WILL GIVE ME EXTRA ATRAC RECORDS THAT I DON'T NEED */ /* THIS JOIN YIELDED 10,853 RECORDS WHICH IS 853 MORE THAN EXPECTED */ /* ADDITIONALLY, IT ISN'T LIKELY THAT EVERY ATRAC FILE WAS MATCHED TO A CLAIM */ PROC SQL; CREATE TABLE SVP_A AS SELECT DISTINCT a.ID, a.MemberID, a.ENRLE_ID, a.ICDDisposition, a.ChaseID, a.ClaimID, a.CDF_DX, a.SVCG_PVDR_NPI_NBR, a.SVCG_PVDR_PIN_TXT, a.SVCG_PVDR_TIN_NBR, a.STMT_STRT_DT, a.STMT_STOP_DT, b.ENRLE_ID, b.CLM_ID, b.SRC_CLM_ID, b.SVCG_PVDR_NPI_NBR, b.SVCG_PVDR_PIN_TXT, b.SVCG_PVDR_TIN_NBR, b.STMT_STRT_DT, b.STMT_STOP_DT, b.CLM_DX FROM CCR2021A a FULL JOIN CLAIMSA b ON a.ENRLE_ID=B.ENRLE_ID AND a.STMT_STRT_DT=b.STMT_STRT_DT AND a.STMT_STOP_DT=b.STMT_STOP_DT AND a.SVCG_PVDR_NPI_NBR=b.SVCG_PVDR_NPI_NBR; QUIT; /* THIS TABLE JOINS CLAIMS AND ATRAC REGARDLESS OF DX */ /* SINCE WE ONLY WANT INFORMATION ON CLAIMS THAT WERE SENT TO THE VENDOR */ /* AND MATCH OUR VENDOR FILE VIA MBR ID, DOS & NPI, */ /* IF YOU DO AN INNER JOIN, WE ONLY GET WHAT MATCHED IN BOTH */ /* A LEFT JOIN WILL GIVE ME EVERYTHING FROM THE VENDOR FILE AND ONLY */ /* WHAT MATCHES IN CLAIMS-THIS WILL GIVE ME EXTRA ATRAC RECORDS THAT I DON'T NEED */ /* THIS JOIN YIELDED 10,853 RECORDS WHICH IS 853 MORE THAN EXPECTED */ /* ADDITIONALLY, IT ISN'T LIKELY THAT EVERY ATRAC FILE WAS MATCHED TO A CLAIM */ /* ADDING ANOTHER QUALIFIER HERE, DX CODE */ /* INNER JOIN RESULTS NOT AS EXPECTED, TRY LEFT JOIN THEN FULL */ PROC SQL; CREATE TABLE SVP_A AS SELECT DISTINCT a.ID, a.MemberID, a.ENRLE_ID, a.ICDDisposition, a.ChaseID, a.ClaimID, a.CDF_DX, a.SVCG_PVDR_NPI_NBR, a.SVCG_PVDR_PIN_TXT, a.SVCG_PVDR_TIN_NBR, a.STMT_STRT_DT, a.STMT_STOP_DT, b.ENRLE_ID, b.CLM_ID, b.SRC_CLM_ID, b.SVCG_PVDR_NPI_NBR, b.SVCG_PVDR_PIN_TXT, b.SVCG_PVDR_TIN_NBR, b.STMT_STRT_DT, b.STMT_STOP_DT, b.CLM_DX FROM CCR2021A a INNER JOIN CLAIMSA b ON a.ENRLE_ID=B.ENRLE_ID AND a.STMT_STRT_DT=b.STMT_STRT_DT AND a.STMT_STOP_DT=b.STMT_STOP_DT AND a.SVCG_PVDR_NPI_NBR=b.SVCG_PVDR_NPI_NBR AND a.CDF_DX=b.CLM_DX; QUIT; /* THIS TABLE JOINS CLAIMS AND ATRAC REGARDLESS OF DX */ /* SINCE WE ONLY WANT INFORMATION ON CLAIMS THAT WERE SENT TO THE VENDOR */ /* AND MATCH OUR VENDOR FILE VIA MBR ID, DOS & NPI, */ /* IF YOU DO AN INNER JOIN, WE ONLY GET WHAT MATCHED IN BOTH */ /* A LEFT JOIN WILL GIVE ME EVERYTHING FROM THE VENDOR FILE AND ONLY */ /* WHAT MATCHES IN CLAIMS-THIS WILL GIVE ME EXTRA ATRAC RECORDS THAT I DON'T NEED */ /* THIS JOIN YIELDED 10,853 RECORDS WHICH IS 853 MORE THAN EXPECTED */ /* ADDITIONALLY, IT ISN'T LIKELY THAT EVERY ATRAC FILE WAS MATCHED TO A CLAIM */ /* ADDING ANOTHER QUALIFIER HERE, DX CODE */ /* INNER JOIN RESULTS NOT AS EXPECTED, TRY LEFT JOIN THEN FULL */ /* THIS CODE GIVES US THE BEST OUTPUT IN JOINING CLAIMS TO CDF */ /* a.CDF_DX= ' ' AND b.CLM_DX NE ' '; = DELETE */ /* AND, SHOWS WHICH RECORDS HAVE CLAIM ICD AND NOT CDF ICD WHICH = DELETES */ /* HOWEVER, THIS IS ONLY WHERE CDF ICDS ARE BLANK */ /* ADDITIONALLY, IN CURRENT STATE, THE VENDOR DIDN'T POPULATE THE DX CODE */ /* THEY JUST NOTED IN ICD DISPOSITION THAT IT IS VALID SO THIS IS A FALSE POSITIVE */ /* FUTURE STATE, THERE SHOULDN'T BE ANY BLANK ICDCODES FROM THE VENDOR OR, VERY FEW */ /* SO, IF I REVERSE THE CODE ABOVE, CDF IS NOT BLANK WHILE CLM DX IS BLANK = ADDS */ /* a.CDF_DX NE ' ' AND b.CLM_DX= ' '; = ADDS */ PROC SQL; CREATE TABLE SVP_A AS SELECT DISTINCT a.ID, a.MemberID, a.ENRLE_ID, a.ICDDisposition, a.ChaseID, a.ClaimID, a.CDF_DX, a.SVCG_PVDR_NPI_NBR, a.SVCG_PVDR_PIN_TXT, a.SVCG_PVDR_TIN_NBR, a.STMT_STRT_DT, a.STMT_STOP_DT, b.ENRLE_ID, b.CLM_ID, b.SRC_CLM_ID, b.SVCG_PVDR_NPI_NBR, b.SVCG_PVDR_PIN_TXT, b.SVCG_PVDR_TIN_NBR, b.STMT_STRT_DT, b.STMT_STOP_DT, b.CLM_DX FROM CCR2021A a INNER JOIN CLAIMSA b ON a.ENRLE_ID=B.ENRLE_ID AND a.STMT_STRT_DT=b.STMT_STRT_DT AND a.STMT_STOP_DT=b.STMT_STOP_DT AND a.SVCG_PVDR_NPI_NBR=b.SVCG_PVDR_NPI_NBR AND a.CDF_DX NE ' ' AND b.CLM_DX= ' '; QUIT; /* THIS TABLE JOINS CLAIMS AND ATRAC REGARDLESS OF DX */ /* SINCE WE ONLY WANT INFORMATION ON CLAIMS THAT WERE SENT TO THE VENDOR */ /* AND MATCH OUR VENDOR FILE VIA MBR ID, DOS & NPI, */ /* IF YOU DO AN INNER JOIN, WE ONLY GET WHAT MATCHED IN BOTH */ /* A LEFT JOIN WILL GIVE ME EVERYTHING FROM THE VENDOR FILE AND ONLY */ /* WHAT MATCHES IN CLAIMS-THIS WILL GIVE ME EXTRA ATRAC RECORDS THAT I DON'T NEED */ /* THIS JOIN YIELDED 10,853 RECORDS WHICH IS 853 MORE THAN EXPECTED */ /* ADDITIONALLY, IT ISN'T LIKELY THAT EVERY ATRAC FILE WAS MATCHED TO A CLAIM */ /* ADDING ANOTHER QUALIFIER HERE, DX CODE */ /* INNER JOIN RESULTS NOT AS EXPECTED, TRY LEFT JOIN THEN FULL */ /* THIS CODE GIVES US THE BEST OUTPUT IN JOINING CLAIMS TO CDF */ /* a.CDF_DX= ' ' AND b.CLM_DX NE ' '; = DELETE */ /* AND, SHOWS WHICH RECORDS HAVE CLAIM ICD AND NOT CDF ICD WHICH = DELETES */ /* HOWEVER, THIS IS ONLY WHERE CDF ICDS ARE BLANK */ /* ADDITIONALLY, IN CURRENT STATE, THE VENDOR DIDN'T POPULATE THE DX CODE */ /* THEY JUST NOTED IN ICD DISPOSITION THAT IT IS VALID SO THIS IS A FALSE POSITIVE */ /* FUTURE STATE, THERE SHOULDN'T BE ANY BLANK ICDCODES FROM THE VENDOR OR, VERY FEW */ /* SO, IF I REVERSE THE CODE ABOVE, CDF IS NOT BLANK WHILE CLM DX IS BLANK = ADDS */ /* a.CDF_DX NE ' ' AND b.CLM_DX= ' '; = ADDS */ /* WILL SETTING CDF_DX NE TO CLM_DX GIVE ME DELETES? */ PROC SQL; CREATE TABLE SVP_A AS SELECT DISTINCT a.ID, a.MemberID, a.ENRLE_ID, a.ICDDisposition, a.ChaseID, a.ClaimID, a.CDF_DX, a.SVCG_PVDR_NPI_NBR, a.SVCG_PVDR_PIN_TXT, a.SVCG_PVDR_TIN_NBR, a.STMT_STRT_DT, a.STMT_STOP_DT, b.ENRLE_ID, b.CLM_ID, b.SRC_CLM_ID, b.SVCG_PVDR_NPI_NBR, b.SVCG_PVDR_PIN_TXT, b.SVCG_PVDR_TIN_NBR, b.STMT_STRT_DT, b.STMT_STOP_DT, b.CLM_DX FROM CCR2021A a INNER JOIN CLAIMSA b ON a.ENRLE_ID=B.ENRLE_ID AND a.STMT_STRT_DT=b.STMT_STRT_DT AND a.STMT_STOP_DT=b.STMT_STOP_DT AND a.SVCG_PVDR_NPI_NBR=b.SVCG_PVDR_NPI_NBR AND a.CDF_DX NE ' ' AND b.CLM_DX NE ' ' AND a.CDF_DX NE b.CLM_DX; QUIT; /* THIS TABLE JOINS CLAIMS AND ATRAC REGARDLESS OF DX */ /* SINCE WE ONLY WANT INFORMATION ON CLAIMS THAT WERE SENT TO THE VENDOR */ /* AND MATCH OUR VENDOR FILE VIA MBR ID, DOS & NPI, */ /* IF YOU DO AN INNER JOIN, WE ONLY GET WHAT MATCHED IN BOTH */ /* A LEFT JOIN WILL GIVE ME EVERYTHING FROM THE VENDOR FILE AND ONLY */ /* WHAT MATCHES IN CLAIMS-THIS WILL GIVE ME EXTRA ATRAC RECORDS THAT I DON'T NEED */ /* THIS JOIN YIELDED 10,853 RECORDS WHICH IS 853 MORE THAN EXPECTED */ /* ADDITIONALLY, IT ISN'T LIKELY THAT EVERY ATRAC FILE WAS MATCHED TO A CLAIM */ /* ADDING ANOTHER QUALIFIER HERE, DX CODE */ /* INNER JOIN RESULTS NOT AS EXPECTED, TRY LEFT JOIN THEN FULL */ /* THIS CODE GIVES US THE BEST OUTPUT IN JOINING CLAIMS TO CDF */ /* a.CDF_DX= ' ' AND b.CLM_DX NE ' '; = DELETE */ /* AND, SHOWS WHICH RECORDS HAVE CLAIM ICD AND NOT CDF ICD WHICH = DELETES */ /* HOWEVER, THIS IS ONLY WHERE CDF ICDS ARE BLANK */ /* ADDITIONALLY, IN CURRENT STATE, THE VENDOR DIDN'T POPULATE THE DX CODE */ /* THEY JUST NOTED IN ICD DISPOSITION THAT IT IS VALID SO THIS IS A FALSE POSITIVE */ /* FUTURE STATE, THERE SHOULDN'T BE ANY BLANK ICDCODES FROM THE VENDOR OR, VERY FEW */ /* SO, IF I REVERSE THE CODE ABOVE, CDF IS NOT BLANK WHILE CLM DX IS BLANK = ADDS */ /* a.CDF_DX NE ' ' AND b.CLM_DX= ' '; = ADDS */ /* 10:44 am 08.13.21 - SAVING SVP_A WITHOUT THE DX QUALIFIER */ /* TO TEST THE DX MATCHING INDEPENDENTLY */ PROC SQL; CREATE TABLE SVP_A AS SELECT DISTINCT a.ID, a.MemberID, a.ENRLE_ID, a.ICDDisposition, a.ChaseID, a.ClaimID, a.CDF_DX, a.SVCG_PVDR_NPI_NBR, a.SVCG_PVDR_PIN_TXT, a.SVCG_PVDR_TIN_NBR, a.STMT_STRT_DT, a.STMT_STOP_DT, b.ENRLE_ID, b.CLM_ID, b.SRC_CLM_ID, b.SVCG_PVDR_NPI_NBR, b.SVCG_PVDR_PIN_TXT, b.SVCG_PVDR_TIN_NBR, b.STMT_STRT_DT, b.STMT_STOP_DT, b.CLM_DX FROM CCR2021A a INNER JOIN CLAIMSA b ON a.ENRLE_ID=B.ENRLE_ID AND a.STMT_STRT_DT=b.STMT_STRT_DT AND a.STMT_STOP_DT=b.STMT_STOP_DT AND a.SVCG_PVDR_NPI_NBR=b.SVCG_PVDR_NPI_NBR; QUIT; DATA SVP_B; SET SVP_A; RUN; /*USING THE DATA IN SVP_A, REMOVED ALL RECORDS WHERE BOTH THE CDF DX AND CLM DX ARE BLANK */ /*CURRENT STATE, THESE COULD BE VALID OR ASRs BUT, FUTURE STATE IF THE CDF DX IS BLANK, */ /*THAT WOULD INDICATE THE VENDOR COULDN'T 'FIND/DETERMINE' A DX CODE */ /*WE MAY WANT TO LOOK AT THESE RECORDS TO IMPROVE OUR CHASE SELECTION */ /* 2,915 RECORDS MET THIS CRITERIA */ PROC SQL; CREATE TABLE SVP_C AS SELECT DISTINCT * FROM SVP_B WHERE CDF_DX = ' ' AND CLM_DX = ' '; QUIT; /*USING SVP_B, REMOVE THE RECORDS IDENTIFIED IN SVP_C */ PROC SQL; CREATE TABLE SVP_D AS SELECT DISTINCT * FROM SVP_B WHERE CDF_DX NE ' ' AND CLM_DX NE ' '; QUIT; /* USING SVP_D WHERE THE CLAIM MATCH IS COMPLETE, CREATE TWO DATASETS */ /* ONE WITH CLAIM INFO AND THE OTHER WITH CDF INFO */ /* FOR THE PURPOSES OF MATCH MERGING THE ICD CODES */ /* MM1 = CDF DATA AND MM2 = CLM DATA */ PROC SQL; CREATE TABLE SVP_MM_1 AS SELECT DISTINCT ID, MemberID, ENRLE_ID, ICDDisposition, ChaseID, ClaimID, CDF_DX AS CLM_DX, SVCG_PVDR_NPI_NBR, SVCG_PVDR_PIN_TXT, SVCG_PVDR_TIN_NBR, STMT_STRT_DT, STMT_STOP_DT FROM SVP_D; QUIT; /* USING SVP_B WHERE THE CLAIM MATCH IS COMPLETE, CREATE TWO DATASETS */ /* ONE WITH CLAIM INFO AND THE OTHER WITH CDF INFO */ /* FOR THE PURPOSES OF MATCH MERGING THE ICD CODES */ /* MM1 = CDF DATA AND MM2 = CLM DATA */ PROC SQL; CREATE TABLE SVP_MM_2 AS SELECT DISTINCT ID, MemberID, ENRLE_ID, ICDDisposition, ChaseID, ClaimID, CLM_DX, CLM_ID, SRC_CLM_ID, SVCG_PVDR_NPI_NBR, SVCG_PVDR_PIN_TXT, SVCG_PVDR_TIN_NBR, STMT_STRT_DT, STMT_STOP_DT FROM SVP_D; QUIT; /* NOW, USING MM1 AND MM2, ATTEMPT TO MERGE AND GET DX MATCH RESULTS */ /* NEED TO SORT EACH TABLE BY CLM_DX*/ /* NEED TO SORT DATASETS BY KEY VARIABLES IN BY STATEMENT*/ PROC SORT DATA=WORK.SVP_MM_1 OUT=SVP_MM_1B; BY CLM_DX; RUN; PROC SORT DATA=WORK.SVP_MM_2 OUT=SVP_MM_2B; BY CLM_DX; data VAL_1; merge SVP_MM_1B(in=in1) SVP_MM_2B(in=in2); by CLM_DX CLM_DX ; length match_type $8 ; if in1 and in2 then match_type='BOTH'; else if in2 then match_type='ADD'; else match_type='DELETE'; run; ... View more

Okay, so this sort of works, it just needs some tweeking...   /* 1. Get ATRAC data*/ PROC SQL; CREATE TABLE CCR2021 AS SELECT ID, MemberID, MemberIndividualID, ProviderType, DateOfServiceFrom, DateOfServiceTo, RenNPI, ICDDisposition, RenTIN, RenPIN, PID, ChartName, ChaseID, ClaimID, ICDCODE as CLM_DX FROM atrac.TBLIMPORTEDRAPSDATA2020(obs=10000); QUIT; /* 2. Get Claim data*/ PROC SQL; CREATE TABLE CLAIMS_1 AS SELECT a.ENRLE_ID, a.CLM_ID, a.SRC_CLM_ID, a.SRC_CNCTNTD_UNQ_ID, a.SVCG_PVDR_NPI_NBR, a.SVCG_PVDR_PIN_TXT, a.SVCG_PVDR_TIN_NBR, a.STMT_STRT_DT, a.STMT_STOP_DT, b.ICD9_DX_CD as CLM_DX FROM R2020_2.CLAIMS_RAW a, R2020_2.RRODS_CLAIM_DX b WHERE ICD9_DX_CD=compress(ICD9_DX_CD,".") AND a.CLM_ID = b.CLM_ID ; QUIT; /* 3. CDF TO CLAIMS VALIDATION*/ /* NEED TO SORT DATASETS BY KEY VARIABLES IN BY STATEMENT*/ PROC SORT DATA=WORK.CLAIMS_1 OUT=CLAIMS_1B; BY CLM_DX; RUN; PROC SORT DATA=WORK.CCR2021 OUT=CCR2021_B; BY CLM_DX; RUN; data VALIDATION; merge CLAIMS_1B(in=in1) CCR2021_B(in=in2); by CLM_DX CLM_DX ; length match_type $8 ; if in1 and in2 then match_type='BOTH'; else if in2 then match_type='ADD'; else match_type='DELETE'; run; Here is a small portion of the output of the VALIDATION table - I have to add dummy code over Protected Health Info. ENRLE_ID CLM_ID SRC_CLM_ID SRC_CNCTNTD_UNQ_ID SVCG_PVDR_NPI_NBR SVCG_PVDR_PIN_TXT SVCG_PVDR_TIN_NBR STMT_STRT_DT STMT_STOP_DT CLM_DX ID MemberID MemberIndividualID DateOfServiceFrom DateOfServiceTo RenNPI ICDDisposition RenTIN RenPIN ChaseID ClaimID match_type . .     .   . . .   1 66 1 10-Jun-20 10-Jun-20 1 Valid a 9 924 32 ADD . .     .   . . .   2 70 53 14-Apr-20 14-Apr-20 2 Valid b 509 928 PY ADD . .     .   . . .   3 81 64 3-May-20 3-May-20 0 Valid c 509 934 K7 ADD . .     .   . . .   4 9 30 24-Jul-20 24-Jul-20 3 Valid d 409 939 KV ADD . .     .   . . .   5 9 30 24-Jul-20 24-Jul-20 3 Valid d 409 939 KV ADD . .     .   . . .   6 31 77 17-Jul-20 17-Jul-20 4 Valid e 309 941 S4 ADD   So, the code is outputing exactly what I asked for but, what I asked for (in the code) isn't enough because, 1. I need to join the datasets by multiple factors, as follows: WHERE a.ENRLE_ID=b.MemberIndividualID AND a.STMT_STRT_DT=DateOfServiceFrom AND a.STMT_STOP_DT=DateOfServiceTo AND a.SVCG_PVDR_NPI_NBR=RenNPI; and then, after that is satisfied I can move onto validating the dx code against the two tables. This may confuse everyone more but,    1. the orig. CCR2021 file - Notice there is no CLM_DX code listed, this means the vendors were not able to identify a Dx or, that thier Dx matches our claims. CCR2021               ID MemberIndividualID DateOfServiceFrom DateOfServiceTo RenNPI RenTIN RenPIN CLM_DX 2 6 4-May-20 4-May-20 A B C     2. the orig. Claims file -  you can see that the date of service doesn't match the date of service in the vendor file(CCR2021) nor did the RenNPI, RenTIN or RenPin (PVDR NPI, PIN, TIN) ENRLE_ID CLM_ID SRC_CLM_ID SVCG_PVDR_NPI_NBR SVCG_PVDR_PIN_TXT SVCG_PVDR_TIN_NBR STMT_STRT_DT STMT_STOP_DT CLM_DX 6 68 EMACHCRP2 6386 26 39 25-Feb-20 25-Feb-20 I10 6 95 EMACHCRP2 6386 26 39 25-Feb-20 25-Feb-20 I10 6 112 EMACHCRP2 6386 26 39 25-Feb-20 25-Feb-20 I10 6 272 E3ACF76DN 213 96 91 25-Feb-20 25-Feb-20 I10 6 950 EJPCG7WY1 1180 44 25 25-Feb-20 25-Feb-20 I10 6 975 EJPCG7WY1 1180 44 25 25-Feb-20 25-Feb-20 I10   3. but the validation code only merged/joined by dx code so, instead of this record being excluded from my final table, it gave it an ADD ENRLE_ID CLM_ID SRC_CLM_ID SRC_CNCTNTD_UNQ_ID SVCG_PVDR_NPI_NBR SVCG_PVDR_PIN_TXT SVCG_PVDR_TIN_NBR STMT_STRT_DT STMT_STOP_DT CLM_DX ID MemberIndividualID DateOfServiceFrom DateOfServiceTo RenNPI ICDDisposition RenTIN RenPIN match_type . .     .   . . .   2 6 4-May-20 4-May-20 A   B C ADD   What i'm looking for is: If the dx in the vendor file (CCR2021) is in the claims file, if no, then add the dx to the claim file If there is a dx on the claim file that isn't on the vendor file then, remove the dx from the claim file.   in this example, if we assume the dates of service match as well as the other factors, there is no dx code listed in the vendor file, the claims file as dx I10 so, the resulting output would need to be to DELETE the I10 dx code from the claim.   Any insight is greatly appreciated!   For fun (yours, not mine 😉 ), A colleague provided this code based on previous years attempts to validate this information - I have not completed all the steps in my code yet so this one goes beyond the scope of my question.     With this code I have an issue with how they obtained a delete file with that join and have confidence that it is correct. /* GET ATRAC DATA */ data CCR2021;set atrac.TBLIMPORTEDRAPSDATA2020 (obs=10000); keep ID MemberID MemberIndividualID ProviderType DateOfServiceFrom DateOfServiceTo RenNPI ICDCode ICDDisposition icddispositionreason RenTIN RenPIN PID ChartName ChaseID; run; /* REORG ATRAC DATA & ASSIGN NEW DISPOSITION */ data CCR2021_new;set CCR2021; ICDCode_new=ICDCode; IF ICDDisposition='Delete' then do; ICDCode_new = ''; ICDDisposition_NEW='ASR'; end; else if ICDCode ne '' then ICDDisposition_NEW='Add'; else ICDDisposition_NEW='ASR'; if icddispositionreason='DOS not found' then delete; run; /*JOIN CLAIMS WITH ATRAC DATA */ proc sql; create table SVP_Claim_Base as select distinct a.ENRLE_ID,a.CLM_ID,a.SRC_CLM_ID,a.SRC_CNCTNTD_UNQ_ID, a.SVCG_PVDR_NPI_NBR,a.SVCG_PVDR_TIN_NBR,a.SVCG_PVDR_PIN_TXT, a.STMT_STRT_DT, a.STMT_STOP_DT from r2020_2.claims_raw a join CCR2021_new b on b.MemberIndividualID=a.enrle_id; quit; /*ADD CLAIM DX CODE TO CLAIMS & ATRAC DATA */ proc sql; create table Claim_match as select distinct a.*,compress(C.ICD9_DX_CD,'.') as Clm_DX, b.ChaseID as CCR_chaseID from SVP_Claim_Base a left JOIN r2020_2.rrods_claim_dx C ON A.CLM_ID = C.CLM_ID inner join CCR2021_new b on b.MemberIndividualID=a.enrle_id and b.DateOfServiceFrom=a.STMT_STRT_DT and b.DateOfServiceTo=a.STMT_STOP_DT and b.RenNPI=a.SVCG_PVDR_NPI_NBR; quit; /* COMPARE DX VIA JOIN CLAIM MATCH & ATRAC */ proc sql; create table Claim_match1 as select distinct a.*,b.ID,b.ICDCode as ICD_CCR,b.ICDDisposition, b.ICDCode_new as ICD_CCR_new ,b.ICDDisposition_NEW from Claim_match a left join CCR2021_new b on b.MemberIndividualID=a.enrle_id and b.DateOfServiceFrom=a.STMT_STRT_DT and b.DateOfServiceTo=a.STMT_STOP_DT and b.RenNPI=a.SVCG_PVDR_NPI_NBR and a.Clm_DX=b.ICDCode_new; quit; /* JOIN CLAIM MATCH 1 TO CONDITION CATEGORIES */ proc sql; create table Claim_match_HCC as select distinct a.*,b.CC from Claim_match1 a join iii._CC b on a.Clm_DX = b.icd; quit; /* ISOLATE DELETES */ data Delete; set Claim_match_HCC; if ICD_CCR='' then Delete='Y'; run; proc sql; select count(MemberIndividualID) from WORK.CCR2021_NEW WHERE ICDDisposition_NEW = 'DOS not found'; quit;     ... View more

Thank you Tom!  I think this is exactly what i'm looking for - I'll run some tests and let you know! ... View more

Thank you Tom, I believe this is exactly what I was looking for - I'll run some tests and let you know!     ... View more

Okay, sorry, I am new to both SAS & Posting SAS related questions.   So, I have an internal claims file and a vendor claims file (which is our file that they added data to). I need to determine if the dx codes they populated should be added to our original claim, deleted from our original claim or are the same as our original claim.  This is where the if/then code comes in.  If the dx code is on the vendor file but not our file then we need to add that dx to the original claim.  If there is a code on our original file that doesn't appear on the vendor file then it needs to be deleted from the original claim.     */get claim data from vendor - file = CDF*/ data CCR2021;set atrac.TBLIMPORTEDRAPSDATA2020 (obs=1000); keep ID MemberID MemberIndividualID ProviderType DateOfServiceFrom DateOfServiceTo RenNPI ICDCode ICDDisposition RenTIN RenPIN PID ChartName ChaseID ClaimID; run; /*save CDF file*/ data CCR2021;set CCR2021; src_clm_id=scan(claimid,2,'_'); run; /*extract dx code from db2/claims data*/ proc sql; connect to db2(database='DRRAP000' &_log_inf); CREATE TABLE CLAIM_MATCH_2 as %put &sqlxmsg; select * from connection to db2 (select distinct a. clm_id, a. src_clm_id, b. icd9_dx_cd from grra.claim a join grra.clm_x_dx b on a.clm_id = b.clm_id where a.stmt_strt_dt >= '2020-01-01' and a.stmt_stop_dt <= '2020-12-31' with UR); %put &sqlxmsg; disconnect from db2; quit; /*join claim dx with raw claims data to get full internal claims data with dx code*/ proc sql; create table Claim_match_3 as selet distinct a.*, b.icd9_dx_cd from r2020_2.claims_raw a left join Claim_match_2 b on a.src_clm_id=b.src_clm_id; quit; /*Join internal claims data with vendors 'claims' data [Claims to CDF]*/ /*Join by member ID, DOS, Provider NPI*/ proc sql;create table Claim_match_Final as select distinct a.*,b.SRC_CNCTNTD_UNQ_ID,SVCG_PVDR_NPI_NBR,SVCG_PVDR_PIN_TXT,SVCG_PVDR_TIN_NBR, icd9_dx_cd from CCR2021 a left join claim_match_3 b on a.MemberIndividualID=b.enrle_id and a.DateOfServiceFrom=b.STMT_STRT_DT and a.DateOfServiceTo=b.STMT_STOP_DT and a.RenNPI=b.SVCG_PVDR_NPI_NBR; quit; /*compare dx codes in vendor file (CDF) to internal file (claims*/ /*if dx code is in CDF but not in Claims the result is ADD*/ /*if dx code is NOT in CDF but IS in Claims the result is DELETE*/ data Delete;set WORK.CLAIM_MATCH_FINAL; if ICDCode=compress(ICD9_DX_CD,".") then ICDDisposition= 'TEST-No_ACTION'; if ICDCode~=compress(ICD9_DX_CD,".") then ICDDisposition= 'TEST-ADD'; if compress(ICD9_DX_CD,".")~=ICDCode then ICDDisposition= 'TEST_DELETE'; run;   OUTPUT:  There can be multiple line items for each member as each dx code for each provider for every date of service gets its own record.  I use Compress to remove the . from the ICD9_dx_cd, if there is a better way, I'm open to it. MemberID ClaimID ProviderType DateOfServiceFrom DateOfServiceTo ICDCode ICDDisposition ICD9_DX_CD 47 103_ERN Optometry 26-Jun-20 26-Jun-20   TEST_DELET H04.123 47 103_ERN Optometry 26-Jun-20 26-Jun-20   TEST_DELET H40.1131 83 103_E8F Family Practice 5-Feb-20 5-Feb-20   TEST_DELET G89.29 83 103_E8F Family Practice 5-Feb-20 5-Feb-20   TEST_DELET I11.9 83 103_E8F Family Practice 5-Feb-20 5-Feb-20   TEST_DELET L73.9 83 103_E8F Family Practice 5-Feb-20 5-Feb-20   TEST_DELET M54.16 83 103_E8F Family Practice 5-Feb-20 5-Feb-20   TEST_DELET M79.672 83 103_E8P Family Practice 8-Jul-20 8-Jul-20   TEST_DELET M53.9 83 103_E8P Family Practice 8-Jul-20 8-Jul-20   TEST_DELET M54.16 37 103_E0P Internal Medicine 21-Jan-20 21-Jan-20 L97921 TEST-No_AC L97.921 37 103_EC3 Internal Medicine 5-Feb-20 5-Feb-20 L97821 TEST_DELET B95.7 37 103_EC3 Internal Medicine 5-Feb-20 5-Feb-20 L97821 TEST_DELET L03.116 37 103_EC3 Internal Medicine 5-Feb-20 5-Feb-20 L97821 TEST-No_AC L97.821 37 103_EC3 Internal Medicine 5-Feb-20 5-Feb-20 L97821 TEST_DELET Z85.828 37 103_EPA Internal Medicine 15-Jan-20 15-Jan-20 L97821 TEST_DELET B95.7 37 103_EPA Internal Medicine 15-Jan-20 15-Jan-20 L97821 TEST_DELET L03.116 37 103_EPA Internal Medicine 15-Jan-20 15-Jan-20 L97821 TEST-No_AC L97.821 37 103_EPA Internal Medicine 15-Jan-20 15-Jan-20 L97821 TEST_DELET Z85.828 37 103_EJF Internal Medicine 6-Jan-20 6-Jan-20 L97821 TEST_DELET B95.7 37 103_EJF Internal Medicine 6-Jan-20 6-Jan-20 L97821 TEST_DELET L03.116 37 103_EJF Internal Medicine 6-Jan-20 6-Jan-20 L97821 TEST-No_AC L97.821 37 103_EJF Internal Medicine 6-Jan-20 6-Jan-20 L97821 TEST_DELET Z85.828 8 103_ESY SURGERY,GENERAL 28-May-20 28-May-20   TEST_DELET K40.91 8 103_ESY SURGERY,GENERAL 28-May-20 28-May-20   TEST_DELET Z68.25 8 103_EGJ SURGERY,GENERAL 19-Jun-20 19-Jun-20   TEST_DELET K40.91 8 103_EGJ SURGERY,GENERAL 19-Jun-20 19-Jun-20   TEST_DELET K40.91 8 103_E8Y Internal Medicine 9-Jun-20 9-Jun-20   TEST_DELET E83.52 8 103_E8Y Internal Medicine 9-Jun-20 9-Jun-20   TEST_DELET I10 8 103_E8Y Internal Medicine 9-Jun-20 9-Jun-20   TEST_DELET K45.0 8 103_E8Y Internal Medicine 9-Jun-20 9-Jun-20   TEST_DELET N18.9 8 103_E8Y Internal Medicine 9-Jun-20 9-Jun-20   TEST_DELET Z01.818 8 103_E8Y Internal Medicine 9-Jun-20 9-Jun-20   TEST_DELET Z68.25 50 103_E33 CARDIO 1-Jul-20 1-Jul-20 J45909 TEST_DELET E66.9 50 103_E33 CARDIO 1-Jul-20 1-Jul-20 J45909 TEST_DELET I10 50 103_ELY1 CARDIO 11-Mar-20 11-Mar-20 J45909 TEST_DELET I10 50 103_ELY1 CARDIO 11-Mar-20 11-Mar-20 J45909 TEST_DELET I83.813 50 103_E4Y1 CARDIO 2-Jan-20 2-Jan-20 J45909 TEST_DELET E66.9 50 103_E4Y1 CARDIO 2-Jan-20 2-Jan-20 J45909 TEST_DELET I83.813 50 103_E4Y1 CARDIO 2-Jan-20 2-Jan-20 J45909 TEST-No_AC J45.909 50 103_E4Y1 CARDIO 2-Jan-20 2-Jan-20 J45909 TEST_DELET M16.11 50 103_E4Y1 CARDIO 2-Jan-20 2-Jan-20 J45909 TEST_DELET R94.31 50 103_ELF CARDIO 23-Apr-20 23-Apr-20 J45909 TEST_DELET I10 50 103_ELF CARDIO 23-Apr-20 23-Apr-20 J45909 TEST_DELET I83.813 50 103_ESY CARDIO 4-Feb-20 4-Feb-20 J45909 TEST_DELET E66.9 50 103_ESY CARDIO 4-Feb-20 4-Feb-20 J45909 TEST_DELET I83.813 50 103_ESY CARDIO 4-Feb-20 4-Feb-20 J45909 TEST_DELET M16.11   Since running this, I realize I have a situation where I have an ADD and a DELETE for 1 record, for example member 37, I would delete almost every code in the ICD9 field and ADD code in the ICD field.   I've also tried a number of Joins, a full join gets me close but still not the view/results i'm looking for -    proc sql;create table Claim_match_Final_A_2 as select distinct a.*,b.SRC_CNCTNTD_UNQ_ID,SVCG_PVDR_NPI_NBR,SVCG_PVDR_PIN_TXT,SVCG_PVDR_TIN_NBR, icd9_dx_cd from CCR2021 a Full join claim_match_4 b on a.MemberIndividualID=b.enrle_id and a.DateOfServiceFrom=b.STMT_STRT_DT and a.DateOfServiceTo=b.STMT_STOP_DT and a.RenNPI=b.SVCG_PVDR_NPI_NBR and compress(B.ICD9_DX_CD,".")=A.ICDCode; quit; MemberIndividualID ClaimID DateOfServiceFrom DateOfServiceTo ICDCode ICD9_DX_CD 200 103_ECA 14-Mar-20 14-Mar-20 E119 E11.9 200 103_ECA 14-Mar-20 14-Mar-20 E113519   200 103_E2F 12-Sep-20 12-Sep-20 E113519     In this example, the bottom 2 (E113519) would be ADD and the top one E119 appears to be No Action Needed as they are equal however, it is actually a delete, the code is not present in the vendor file & ICDCode      ... View more