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;
