Select Distinct Duplicate values

markjc — Thu, 04 May 2017 18:11:32 GMT

I'm trying to get Distinct values when there are duplicates.

The sample data shows phone number(s) customers use when they call in to their account.

I want to select accounts where the customer calls in from more than one phone number.

I'm able to get the resuls by running 2 queries. I was just wondering if there is a better method.

Data Call_Data;

input call_date Account_Num $11. Call_Number $11.;

datalines;

20170428 33050021993 3055550358

20170426 33050021993 3055550358

20170412 33050030788 9725552607

20170502 33050381306 4405554080

20170501 33050381306 4405554080

20170429 33050381306 4405554086

20170426 33050482922 7035553110

20170424 33050482922 7035553110

20170422 33050482922 7035551708

20170421 33050482922 7035551708

;

Run;

/* Accounts with 2 phone numbers */

Proc sql; create table dup as

select Account_Num,

count(distinct(call_number)) as count

from Call_Data

group by Account_Num

having count >1 ;

quit;

Results

Account_Num Count

33050381306 2

33050482922 2

/* Get Distinct Account and Phone Number when the customer calls in using 2 different numbers */

proc sql;

Create table dup_ph as

select distinct Account_Num, call_number

from Call_Data

Where Account_Num in (select Account_Num from dup ) ;

Quit;

Desired Results

Account_Num Call_Number

33050381306 4405554080

33050381306 4405554086

33050482922 7035551708

33050482922 7035553110

/* Tried to run with a subquery but received

ERROR: A subquery cannot select more than one column. */

proc sql;

create table dup_ph as

Select distinct Account_Num, call_number

From Call_Data

Where Account_Num in (

select Account_Num,

count(distinct(call_number)) as count

from Call_Data

group by Account_Num

having count >1) ;

Quit;

Re: Select Distinct Duplicate values

kiranv_ — Thu, 04 May 2017 18:33:36 GMT

change it to below and your subquery should work

proc sql;

create table dup_ph as

Select distinct Account_Num, call_number

From Call_Data

Where Account_Num in (

select Account_Num

from Call_Data

group by Account_Num

having count(distinct(call_number)) >1) ;

Re: Select Distinct Duplicate values

markjc — Thu, 04 May 2017 18:34:08 GMT

So very smart of you. A simple solution I wouldn't have known to do.

Re: Select Distinct Duplicate values

kiranv_ — Thu, 04 May 2017 18:43:14 GMT

thank you and if you think this has solved the problem then please mark it as solution

topic Re: Select Distinct Duplicate values in SAS Programming

Select Distinct Duplicate values

Re: Select Distinct Duplicate values

Re: Select Distinct Duplicate values

Re: Select Distinct Duplicate values