Hello
For each customer there is one ID or multiple ID's.
I would like to create same key for customers that have at least one common ID.
For example:
Create same key to customers 111111 and 222222
Create same key to customers 777777 and 888888
For other customers will create for each customer another key because they dont share any ID.
What is best way to do it ?
Data have;
Input Customer ID ;
cards;
111111 1
111111 2
222222 1
333333 4
444444 5
555555 6
777777 8
888888 8
;
run;
You already have such a key, it is the variable ID. No need to create something redundant.
I will change a bit the question:
I added also team variable.
The traget- For customers belong to team='b' (3 customers) I want to find the list of "dual customers from team a".
Data have;
Input Customer ID team $;
cards;
111111 1 a
111111 2 a
222222 1 b
333333 4 a
444444 5 b
555555 6 a
777777 8 b
888888 8 a
;
run;
@Ronein wrote:
The traget- For customers belong to team='b' (3 customers) I want to find the list of "dual customers from team a".
Not sure I understand the question, can you show what the expected result is for your sample data and explain how you got there.
It would always help to clarify what you want if you also provide the desired result based on sample data provided.
@Ronein wrote:The traget- For customers belong to team='b' (3 customers) I want to find the list of "dual customers from team a".
Assuming what you want I had to change the sample data you provided to then create below report. Is this what you're after?
data have;
Input Customer ID team $;
cards;
111111 1 a
111111 2 b
222222 1 b
333333 4 a
444444 5 b
555555 6 a
777777 8 b
888888 8 a
;
title 'Customers belonging to more than one team';
proc sql;
select
*,
count(distinct team) as n_teams
from have
group by customer
having count(distinct team)>1
;
quit;
title;
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 25. 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.