I have used distinct statement but still getting same numbers.

[Sandeep77]

Hi Experts,

I have been trying to find the distinct dcphonenumbers and I have used select distinct but in the output data, it gives me duplicate dcphonenumbers also. Can you please check and suggest what's the mistake in the code?

proc sql;
create table Dialled_numbers as
select distinct
a.*,
b.dcPhoneNumber
from work.confirmed_email as a
inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber;
quit;

LOG
29         proc sql;
30         create table Dialled_numbers as
31         select distinct
32         a.*,
33         b.dcPhoneNumber
34         
35         from work.confirmed_email as a
36         inner join DIALFILE.tDiallerCalls as b on a.account_number=b.dcaccountnumber
37         ;
NOTE: Compressing data set WORK.DIALLED_NUMBERS decreased size by 90.69 percent. 
      Compressed is 1679 pages; un-compressed would require 18034 pages.
NOTE: Table WORK.DIALLED_NUMBERS created, with 721344 rows and 14 columns.

[PaigeMiller · Accepted solution]

DISTINCT works on the combination of ALL variables in the query, the variables from A and the variables from B. Whatever is in a.* is not distinct, even though the phone numbers are the same. That's why phone numbers can appear more than once.

If you just want a list of distinct phone numbers use table B and only the variable dcPhoneNumber. However, I suspect that's not what you want either, and so @Sandeep77 it ALWAYS helps (that's ALWAYS) if we know the final result you want, and we don't know that, you haven't told us.

[Sandeep77]

I just wanted the unique counts of the phone numbers. So I removed a.*, and ran the query with select distinct b.dcPhoneNumber and it worked.

Thanks again!