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;
thank you and if you think this has solved the problem then please mark it as solution
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) ;
So very smart of you. A simple solution I wouldn't have known to do.
thank you and if you think this has solved the problem then please mark it as solution
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: