Duplicate values are removed without consent.

[Wken1122]

Unfortunately, the result is still the same...

[Kurt_Bremser]

We are all making guesses here, and then put shots into the dark.

You HAVE to post example data that illustrates the issue; without that, we won't be able to really help you.

Just some records from both datasets that show the effect you experience. 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 example datasets to data steps that can be posted here.

[Wken1122]

Hi,
The following is the a sample of the data sets.
For CH_P02_SOURCE_5, there are too many variables and the records that could match with the sample data for ODS_CLAIM is pretty way back(1000+ rows) so i have narrow it down and only provide the POLICYNUMBER. Hopefully its enough.

data WORK.POLICYNUMBER;
  
infile datalines dsd truncover;
  
input POLICYNUMBER:$12.;

datalines4;

136838

281600

500035316

500035233

299719

340570

276089

81125
;;;;

data WORK.ODS_CLAIM;
  
infile datalines dsd truncover;
  
input ID:$19. POLICYNUMBER:$12. PRODUCTC:$3. CLAIMNUMBER:$14. CLAIMNOTIFIEDDATE:DATETIME. STATUS:$30. LOSSDATE:DATETIME. OCCURENCEDATE:DATETIME. LOSSTYPE:$44. TOTALPAIDAMOUNT:BEST12. LOSSDESCRIPTION:$52. INTIMATEDDATE:DATETIME. CLOSEDATE:DATETIME. TRANSACTIONNUMBER:$14. DATEADMITTED:DATETIME. DATEAPPROVED:DATETIME. DATEDISCHARGED:DATETIME. APPROVEDAMOUNT:BEST12. ILLNESSCODE:$5. ILLNESS:$160. LOSSDESCRIPTIONCODE:BEST12. CAUSEOFEVENTCODE:$3. CLAIMENTRYDATE:DATETIME.;

datalines4;

I303-4912889117,203083,EIB,1,30NOV95:00:00:00,Fully Paid,11NOV95:00:00:00,,HOSPITAL BENEFIT,175,,,,,11NOV95:00:00:00,30NOV95:00:00:00,18NOV95:00:00:00,175,101,ANGINA PECTORIS - CHEST PAIN,,,

I303-4912882391,237438,EIB,1,19APR95:00:00:00,Fully Paid,22MAR95:00:00:00,,HOSPITAL BENEFIT,350,,,,,22MAR95:00:00:00,19APR95:00:00:00,29MAR95:00:00:00,350,727,PARASTIC INFECTION,,,

I303-4913303396,276089,EIB,2,08SEP06:00:00:00,Fully Paid,06APR13:00:00:00,,HOSPITAL BENEFIT,3000,,,,,06APR13:00:00:00,08OCT06:00:00:00,06APR24:00:00:00,3000,604,DIABETES MELLITUS,,,

I303-4913389106,276089,EIB,3,07JAN09:00:00:00,Fully Paid,09FEB15:00:00:00,,HOSPITAL BENEFIT,250,,,,,09FEB15:00:00:00,10MAY09:00:00:00,09FEB18:00:00:00,250,113,MYOCARDIAL INFARCTION,,,

I303-4913199216,276089,EIB,1,10DEC04:00:00:00,Fully Paid,04AUG19:00:00:00,,HOSPITAL BENEFIT,200,,,,,04AUG19:00:00:00,10DEC04:00:00:00,04AUG23:00:00:00,200,604,DIABETES MELLITUS,,,

I303-4912889249,279992,EIB,1,02OCT04:00:00:00,Fully Paid,04JAN24:00:00:00,,HOSPITAL BENEFIT,75,,,,,04JAN24:00:00:00,02OCT04:00:00:00,04JAN27:00:00:00,75,423,OTHERS - DIGESTIVE,,,

I303-4912889261,281600,EIB,1,14OCT94:00:00:00,Fully Paid,10FEB94:00:00:00,,HOSPITAL BENEFIT,100,,,,,10FEB94:00:00:00,14OCT94:00:00:00,10JUN94:00:00:00,100,311,HEPATITIS - INFECTIVE & ALCOHOLIC,,,

I303-4912889262,281600,EIB,2,01OCT03:00:00:00,Fully Paid,02DEC18:00:00:00,,HOSPITAL BENEFIT,50,,,,,02DEC18:00:00:00,01OCT03:00:00:00,02DEC20:00:00:00,50,729,"NASAL AILMENTS - SINUSITIES, RHINITIS",,,

I303-4913439139,283895,EIB,3,04APR13:00:00:00,Fully Paid,13FEB25:00:00:00,,HOSPITAL BENEFIT,50,,,,,13FEB25:00:00:00,04OCT13:00:00:00,13FEB27:00:00:00,50,423,OTHERS - DIGESTIVE,,,

I303-4913443756,283895,EIB,4,13MAY29:00:00:00,Fully Paid,13FEB22:00:00:00,,HOSPITAL BENEFIT,75,,,,,13FEB22:00:00:00,06JUL13:00:00:00,13FEB25:00:00:00,75,423,OTHERS - DIGESTIVE,,,
;;;;

If its wrong please tell me. I am quite new to this.
Thank you.

[Kurt_Bremser]

Some empty lines, but otherwise OK 😉

So I ran this code:

data WORK.POLICYNUMBER;
infile datalines dsd truncover;
input POLICYNUMBER:$12.;
datalines4;
136838
281600
500035316
500035233
299719
340570
276089
81125
;;;;
run;

data WORK.ODS_CLAIM;
infile datalines dsd truncover;
input ID:$19. POLICYNUMBER:$12. PRODUCTC:$3. CLAIMNUMBER:$14. CLAIMNOTIFIEDDATE:DATETIME. STATUS:$30. LOSSDATE:DATETIME. OCCURENCEDATE:DATETIME. LOSSTYPE:$44. TOTALPAIDAMOUNT:BEST12. LOSSDESCRIPTION:$52. INTIMATEDDATE:DATETIME. CLOSEDATE:DATETIME. TRANSACTIONNUMBER:$14. DATEADMITTED:DATETIME. DATEAPPROVED:DATETIME. DATEDISCHARGED:DATETIME. APPROVEDAMOUNT:BEST12. ILLNESSCODE:$5. ILLNESS:$160. LOSSDESCRIPTIONCODE:BEST12. CAUSEOFEVENTCODE:$3. CLAIMENTRYDATE:DATETIME.;
datalines4;
I303-4912889117,203083,EIB,1,30NOV95:00:00:00,Fully Paid,11NOV95:00:00:00,,HOSPITAL BENEFIT,175,,,,,11NOV95:00:00:00,30NOV95:00:00:00,18NOV95:00:00:00,175,101,ANGINA PECTORIS - CHEST PAIN,,,
I303-4912882391,237438,EIB,1,19APR95:00:00:00,Fully Paid,22MAR95:00:00:00,,HOSPITAL BENEFIT,350,,,,,22MAR95:00:00:00,19APR95:00:00:00,29MAR95:00:00:00,350,727,PARASTIC INFECTION,,,
I303-4913303396,276089,EIB,2,08SEP06:00:00:00,Fully Paid,06APR13:00:00:00,,HOSPITAL BENEFIT,3000,,,,,06APR13:00:00:00,08OCT06:00:00:00,06APR24:00:00:00,3000,604,DIABETES MELLITUS,,,
I303-4913389106,276089,EIB,3,07JAN09:00:00:00,Fully Paid,09FEB15:00:00:00,,HOSPITAL BENEFIT,250,,,,,09FEB15:00:00:00,10MAY09:00:00:00,09FEB18:00:00:00,250,113,MYOCARDIAL INFARCTION,,,
I303-4913199216,276089,EIB,1,10DEC04:00:00:00,Fully Paid,04AUG19:00:00:00,,HOSPITAL BENEFIT,200,,,,,04AUG19:00:00:00,10DEC04:00:00:00,04AUG23:00:00:00,200,604,DIABETES MELLITUS,,,
I303-4912889249,279992,EIB,1,02OCT04:00:00:00,Fully Paid,04JAN24:00:00:00,,HOSPITAL BENEFIT,75,,,,,04JAN24:00:00:00,02OCT04:00:00:00,04JAN27:00:00:00,75,423,OTHERS - DIGESTIVE,,,
I303-4912889261,281600,EIB,1,14OCT94:00:00:00,Fully Paid,10FEB94:00:00:00,,HOSPITAL BENEFIT,100,,,,,10FEB94:00:00:00,14OCT94:00:00:00,10JUN94:00:00:00,100,311,HEPATITIS - INFECTIVE & ALCOHOLIC,,,
I303-4912889262,281600,EIB,2,01OCT03:00:00:00,Fully Paid,02DEC18:00:00:00,,HOSPITAL BENEFIT,50,,,,,02DEC18:00:00:00,01OCT03:00:00:00,02DEC20:00:00:00,50,729,"NASAL AILMENTS - SINUSITIES, RHINITIS",,,
I303-4913439139,283895,EIB,3,04APR13:00:00:00,Fully Paid,13FEB25:00:00:00,,HOSPITAL BENEFIT,50,,,,,13FEB25:00:00:00,04OCT13:00:00:00,13FEB27:00:00:00,50,423,OTHERS - DIGESTIVE,,,
I303-4913443756,283895,EIB,4,13MAY29:00:00:00,Fully Paid,13FEB22:00:00:00,,HOSPITAL BENEFIT,75,,,,,13FEB22:00:00:00,06JUL13:00:00:00,13FEB25:00:00:00,75,423,OTHERS - DIGESTIVE,,,
;;;;
run;

proc sql;
create table claims_details as
select id as claimid, policynumber
from ods_claim
where status in ('Fully Paid')
  and policynumber in (select distinct policynumber from policynumber)
;
quit;

proc print data=claims_details noobs;
run;

and got this result:

    claimid        POLICYNUMBER

I303-4913303396       276089   
I303-4913389106       276089   
I303-4913199216       276089   
I303-4912889261       281600   
I303-4912889262       281600   

You can see that no repeats of the policynumber were removed.

Then I ran your original code:

PROC SQL;
CREATE TABLE CLAIMS_DETAILS AS
(
SELECT ID AS CLAIMID,ODS_CLAIM.POLICYNUMBER,PRODUCTC,CLAIMNUMBER,CLAIMNOTIFIEDDATE,STATUS,OCCURENCEDATE,LOSSTYPE,
TOTALPAIDAMOUNT,INTIMATEDDATE,DATEADMITTED,DATEDISCHARGED,
DATEAPPROVED,LOSSDESCRIPTION,TRANSACTIONNUMBER,APPROVEDAMOUNT,
LOSSDESCRIPTIONCODE,CLAIMENTRYDATE,CAUSEOFEVENTCODE,CLOSEDATE,LOSSDATE,ILLNESSCODE,ILLNESS

FROM ODS_CLAIM, (SELECT DISTINCT(POLICYNUMBER) FROM policynumber) TEMP
WHERE ODS_CLAIM.POLICYNUMBER=TEMP.POLICYNUMBER
AND STATUS IN ('Fully Paid')
);
QUIT;

(only adapted the dataset name), and got the same 5 records.

[Wken1122]

But how come my results are removed?

[Kurt_Bremser]

WHICH results are removed? The code returns exactly what it is supposed to return.

[Wken1122]

I mean my results of the entire data.
I got the same result as yours when using the 10 samples.

[Kurt_Bremser]

Then you need to build samples with the policynumbers/observations that you have the problem with.