Multiple pivoting

Suminder — Wed, 04 Mar 2020 17:07:04 GMT

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

Re: Multiple pivoting

ballardw — Wed, 04 Mar 2020 17:22:51 GMT

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.

topic Multiple pivoting in SAS Programming

Multiple pivoting

Re: Multiple pivoting