topic Re: re: Duplicates in SAS Procedures

re: Duplicates

twildone — Wed, 15 Apr 2015 19:38:11 GMT

Hi, I have created a dataset from two different datasets and I want to remove any duplicates in the new datasets. I have tried using the the proc sql distinct approach as well as a Data step with the first. approach and I keep getting a dataset that has duplicates but some of the duplicates are removed.. The dataset has only 3 variable, ID as a character variable and Unit Price and Effective Date both as numeric variables. I double checked and made sure are and were formatted the same before creating the new dataset. Any suggestion on what I may be missing and should check....Thanks in Avance.

Re: re: Duplicates

Reeza — Wed, 15 Apr 2015 19:50:02 GMT

Your join may have issues.

Otherwise you can check unique/duplicate records in SAS with NOUNIQUEKEY in proc sort. Available in SAS 9.3+

proc sort data=have out=duprec nouniquekey uniqueout=want; by ID; run;

Re: re: Duplicates

ballardw — Wed, 15 Apr 2015 19:54:35 GMT

I would be interested is seeing the SQL code that did not work. Order of when to appear DISTINCT may have been the issue.

Also you may need to look at the ID in case some of them have one or more leading blank characters as "string" is not equal to " string" and could cause apparent duplicates.

Re: re: Duplicates

twildone — Thu, 16 Apr 2015 13:07:26 GMT

Hi Reeza & Ballardw,

Thanks for your help and suggestions. With your suggestions of the possibility of blank characters for the ID variable, it turns out that there must have been a blank or blanks in the Unit Price as I used the Compress Function on both the ID and Unit Price variables having to convert Unit Price to a character variable first. It did the trick. Thanks once againn for your help.