Select Distinct Duplicate values

[markjc]

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;

[kiranv_]

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) ;

[markjc]

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

[kiranv_ · Accepted solution]

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