topic Re: key for dual customers in SAS Programming

key for dual customers

Ronein — Wed, 11 Jan 2023 18:54:38 GMT

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;

Re: key for dual customers

Kurt_Bremser — Wed, 11 Jan 2023 20:46:42 GMT

You already have such a key, it is the variable ID. No need to create something redundant.

Re: key for dual customers

Ronein — Wed, 11 Jan 2023 21:21:55 GMT

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;

Re: key for dual customers

Reeza — Wed, 11 Jan 2023 21:27:21 GMT

@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.

Re: key for dual customers

Patrick — Thu, 12 Jan 2023 06:01:57 GMT

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;