topic Re: Duplicate values are removed without consent. in SAS Studio

Duplicate values are removed without consent.

Wken1122 — Thu, 12 Oct 2017 04:22:57 GMT

Hi,
For some reason, my codes keep on removing the duplicate records which I intended to keep.
The scenario is, I have 2 sets of tables, by using a column(POLICYNUMBER) I would join them together. Both table would contain POLICYNUMBERs and there are some records which are duplicates. After processed by SAS, all the records that fits the requirements of the coding are displayed BUT records that fits the requirements but with duplicate POLICYNUMBER are removed from the final result.

The following is a part of the coding;

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 CH_P02_SOURCE_5) TEMP
WHERE ODS_CLAIM.POLICYNUMBER=TEMP.POLICYNUMBER
AND STATUS IN ('Fully Paid')
);
QUIT;

Sorry if my explanation is a bit confusing cause I am not really sure how to put it into words.
Thank you.

Re: Duplicate values are removed without consent.

Reeza — Thu, 12 Oct 2017 05:12:07 GMT

Note the word DISTINCT. That will select distinct records. And the WHERE clause may remove them as well.

Those are where I'd start checking.

Re: Duplicate values are removed without consent.

Wken1122 — Thu, 12 Oct 2017 06:33:38 GMT

Oh I am sorry, I made a mistake in explaining the scenario.
POLICYNUMBER that are duplicates in one table, SOURCE should be removed.
ALL POLICYNUMBER in the other table should be retained.
That's why DISTINCT is used.

Re: Duplicate values are removed without consent.

Kurt_Bremser — Thu, 12 Oct 2017 06:35:16 GMT

In which of the datasets are duplicates found? If only in CH_P02_SOURCE_5, then the distinct clause will remove them.

Duplicates in ODS_CLAIM will remain, see this short example:

data claims;
input pnr $ clid $;
cards;
12345 111
12345 222
56789 333
76477 444
;
run;

data source;
input pnr $;
cards;
12345
12345
56789
56789
;
run;

proc sql;
create table want as
select claims.*
from claims, (select distinct pnr from source) temp
where claims.pnr = temp.pnr
;
quit;

And stop coding in capitals, that's an eyesore. Shouting at the SAS interpreter isn't necessary 😉

Re: Duplicate values are removed without consent.

Wken1122 — Thu, 12 Oct 2017 07:00:45 GMT

the intent was to remove the duplicates from source 5 first then compare with ods_claims.
So it is ok for ods_claims' data to retain the duplicates but the final result that i got is where ALL the duplicate POLICYNUMBER (from both tables) are removed.

Re: Duplicate values are removed without consent.

Kurt_Bremser — Thu, 12 Oct 2017 07:03:55 GMT

To verify this, we will need example data that illustrates this behaviour. See How to create a Datastep from a Dataset for posting example data.

Re: Duplicate values are removed without consent.

Patrick — Thu, 12 Oct 2017 11:20:58 GMT

@Wken1122

I can't see anything in your SQL that would explain what you observe unless your other part in the join condition filters out the records.

... AND STATUS IN ('Fully Paid')

Could this explain the "lost" records? Have you checked if there is more than one record per policynumber with a status of "Fully Paid"?

Re: Duplicate values are removed without consent.

ballardw — Thu, 12 Oct 2017 15:49:15 GMT

Maybe what you are looking for is a LEFT (or Right) Join on a value.

data one;
   input policynumber;
datalines;
1
1
1
2
2
2
3
;
run;

data two;
   input policynumber value;
datalines;
1  345
1  456
1  678
1  2
1  222
2  123
2  444
3  999
3  888
3  777
4  202
;
run;

proc sql;
   create table want as
   select a.*, b.value
   from (select distinct policynumber from one) as a
        left join
        two as b
        on a.policynumber=b.policynumber
   ;
quit;

Notice that values in two for policynumber that don't appear in set one are not in the result and the ones selected from set two only appear once

Re: Duplicate values are removed without consent.

Wken1122 — Fri, 13 Oct 2017 01:48:15 GMT

There are 1000+ records(after DISTINCT) that are under the status of FULLY PAID yet only 603 of them are generated at SAS.

Re: Duplicate values are removed without consent.

Patrick — Fri, 13 Oct 2017 02:30:29 GMT

@Wken1122

You're using an inner join so the only remaining explanation I'm having is that there are POLICYNUMBER values in the one table which don't exist in the other table.

If variable POLICYNUMBER is character then use upcase(strip(POLICYNUMBER)) before you join.

What you observe must have an explanation in your data. Investigate one of the dropped POLICYNUMBER and check if it really exists in both tables. And if it exists and looks exactly the same in both source tables then also consider non-printable characters - you can remove such characters using the compress() function with the right switches.

...and last but not least: Also "standardize" fully paid:

AND upcase(compress(STATUS)) IN ('FULLYPAID')

Re: Duplicate values are removed without consent.

Wken1122 — Fri, 13 Oct 2017 03:28:42 GMT

@Patrick
I'm sorry, i mistakenly explained the scenario.
For the SOURCE_5, any duplicates value will be removed while duplicate values on the other table, CLAIMS MUST be retained.

Also I had already done the checking, from the SOURCE_5 table, exist a one to many relation to the CLAIMS table, where for example, one insurance policy could have several claims. Therefore, all the duplicate POLICYNUMBER on the SOURCE_5 are removed thru the DISTINCT function and the remaining POLICYNUMBER would and could match with more than one POLICYNUMBER on CLAIMS table.

variable POLICYNUMBER is alphanumeric so does it affect anything?

I had try to standardize STATUS but don't seems to change anything.

Re: Duplicate values are removed without consent.

Patrick — Fri, 13 Oct 2017 04:00:54 GMT

@Wken1122 wrote:

@Patrick
I'm sorry, i mistakenly explained the scenario.
For the SOURCE_5, any duplicates value will be removed while duplicate values on the other table, CLAIMS MUST be retained.

Also I had already done the checking, from the SOURCE_5 table, exist a one to many relation to the CLAIMS table, where for example, one insurance policy could have several claims. Therefore, all the duplicate POLICYNUMBER on the SOURCE_5 are removed thru the DISTINCT function and the remaining POLICYNUMBER would and could match with more than one POLICYNUMBER on CLAIMS table.

variable POLICYNUMBER is alphanumeric so does it affect anything?

I had try to standardize STATUS but don't seems to change anything.

@Wken1122

Yes, I understand, you're joining (inner join) two tables with a 1:M relationship. If you "loose" records then it's because there are values for POLICENUMBER in CLAIMS which don't exist in SOURCE_5.

variable POLICYNUMBER is alphanumeric so does it affect anything?

Then to be on the safe side make sure you strip() or compress() the values as well as uppercase() them BEFORE you use them in the join condition.

Re: Duplicate values are removed without consent.

Wken1122 — Fri, 13 Oct 2017 04:11:09 GMT

DATA CH_P02_SOURCE_5;
SET CH_P02_SOURCE_5;
WHERE COMPRESS(strip(POLICYNUMBER));
RUN;

Am I using it correctly? If yes then it didn't change a thing...

Re: Duplicate values are removed without consent.

Patrick — Fri, 13 Oct 2017 05:00:09 GMT

@Wken1122

If below doesn't return what you expect then I won't be able to help you any further without actually seeing the data.

WHERE 
  upcase(compress(compress(CLAIM.POLICYNUMBER,,'kw')))=upcase(compress(compress(TEMP.POLICYNUMBER,,'kw')))
  and upcase(compress(compress(STATUS,,'kw')))= 'FULLYPAID'

Re: Duplicate values are removed without consent.

Tom — Fri, 13 Oct 2017 05:21:21 GMT

It does not look like you need to do a join.

proc sql;
create table claims_details as
select id as claimid
     , 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
where status in ('Fully Paid')
  and policynumber in (select distinct policynumber from ch_p02_source_5)
;
quit;

Re: Duplicate values are removed without consent.

Wken1122 — Fri, 13 Oct 2017 06:28:27 GMT

Unfortunately, the result is still the same...

Re: Duplicate values are removed without consent.

Kurt_Bremser — Fri, 13 Oct 2017 06:34:28 GMT

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-data-AKA-generate/ta-p/258712 to convert your example datasets to data steps that can be posted here.

Re: Duplicate values are removed without consent.

Wken1122 — Fri, 13 Oct 2017 07:05:39 GMT

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.

Re: Duplicate values are removed without consent.

Kurt_Bremser — Fri, 13 Oct 2017 07:18:13 GMT

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.

Re: Duplicate values are removed without consent.

Wken1122 — Fri, 13 Oct 2017 08:11:10 GMT

But how come my results are removed?