BookmarkSubscribeRSS Feed
Suminder
Calcite | Level 5

Scenario:

There are three Customers that have same phone no.

C1 with Customer Code CD1

C2 with Customer Code CD2

C3 with Customer Code CD3

With same phone number 123456789

 

The source comes as follows

Phone                                   CustCode                            RefCustCode

123456789                           CD1                                        C1

123456789                           CD2                                        C2

123456789                           CD3                                        C3

 

The following output is desired:

Required Table

Code                                     RefCustCode                     RelatedRefCustCode                      Phone

CD1                                        C1                                           C2                                                           123456789          

CD1                                        C1                                           C3                                                           123456789

CD2                                        C2                                           C1                                                           123456789

CD2                                        C2                                           C3                                                           123456789

CD3                                        C3                                           C1                                                           123456789

CD3                                      C3                                         C2                                                        123456789

1 REPLY 1
ballardw
Super User

One way:

data have;
input Phone :$9.  CustCode $ RefCustCode $;
datalines;
123456789    CD1             C1
123456789    CD2             C2
123456789    CD3             C3
;

proc sql;
   create table want as
   select a.custcode, a.refcustcode, b.refcustcode as RelatedCustCode, a.phone
   from have as a 
        left join 
        have as b
        on a.phone=b.phone
   where a.refcustcode ne b.refcustcode
   order by a.custcode, a.refcustcode,RelatedCustCode
   ;
quit;
       

Please note providing the data in the form of a data step that we can execute will get better results.

 

The Proc SQL is joining the same data set to itself using the Phone number to link "like records" and the Where says to keep only the ones where the refcustcodes are different. The Order by creates a specified sort order.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 354 views
  • 0 likes
  • 2 in conversation