Multiple pivoting

[Suminder]

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

[ballardw]

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.