DATA Step, Macro, Functions and more

Select Distinct Duplicate values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Select Distinct Duplicate values

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

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;


Accepted Solutions
Solution
‎05-04-2017 02:49 PM
PROC Star
Posts: 326

Re: Select Distinct Duplicate values

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

View solution in original post


All Replies
PROC Star
Posts: 326

Re: Select Distinct Duplicate values

[ Edited ]

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

Occasional Contributor
Posts: 5

Re: Select Distinct Duplicate values

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

Solution
‎05-04-2017 02:49 PM
PROC Star
Posts: 326

Re: Select Distinct Duplicate values

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 182 views
  • 1 like
  • 2 in conversation