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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: