How to join table properly to only display result with these criteria?

[imdickson]

Hi Guys, i am stuck with this issue where i cannot get the expected result for many days.

I have two tables, Master Well as A and Well Transaction as B.

Table A, Master Well is the master table for all Wells where information such as WELL_RK, WELL_NAME, VALID_TO_DTTM.

Below is the sample data

Table B, Well Transaction where it has data such as WELL_RK, CHANGE_DATE_TIME and etc.

TAKE NOTE A: No WELL_NAME in table B.

TAKE NOTE B: CHOKE_CHANGE_DATE_TIME is also one of the key.

Recently, the Master Well Table A is loaded with new data where all existing WELL's VALID_TO_DTTM set as 2018-Feb-28, which indicates expired. A new set of WELL are loaded, so the VALID_TO_DTTM are 5999-01-01.

The old expired WELL_RK range from 1 to 2500 while new WELL_RK range from 2501 to 5000.

When WELL Transaction table B joined with Master Well table A to get WELL_RK, the Table B get duplicate records.

Right now, WELL Transaction table B will have mostly but not all duplicate records. Imagine WELL_RK 1 and WELL_RK 2501 belongs to the same WELL_NAME in Master Table A but WELL_RK is expired in Master table.

I have come out with my own SQL script to try to only display the results in below possible outcome:
1) If WELL has expired and new records in both tables, only display expired records(For delete later).

2) If WELL only has expired records without new records in table B, no need to display(Nothing to delete later).

proc sql;
create table SASA as
select count(*),WELL_NAME,A.CHOKE_CHANGE_DATE_TIME,* from (   
	select
	      WELL_TRANS.*
		  , WELL.*
	   from
	      ODMSCDS.WELL_TRANS as WELL_TRANS left join 
	      ODMSCDS.WELL as WELL on WELL_TRANS.WELL_RK=WELL.WELL_RK
		  
		  
	     )a

group by A.WELL_NAME, A.CHOKE_CHANGE_DATE_TIME
HAVING COUNT (*) >1
ORDER BY A.WELL_NAME ASC, A.WELL_RK DESC, A.CHOKE_CHANGE_DATE_TIME
   ;
quit;

However, it will never give me the result set that i want.

i NEED help on improving my SQL statement to print out results that i plan to delete in the next stage which are expired and duplicate records.

Thanks in Advanced.

[Kurt_Bremser]

Please post example data in a data step. Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your existing datasets to data steps. Never post pictures of data, as we have better things to do than type data off pictures and guessing about lengths and formats on the way.