topic New post-Recognize accounts with similar ownership ID's in SAS Programming

Recognize accounts with similar ownership ID's

Ronein — Sat, 19 Feb 2022 08:34:41 GMT

Hello

I have the following problem.

There is one data set called Have1 with following columns:

Customer_account , date,amount

This data set includes information on transactions of customers.

There is another data set caled Have2 with the following columns:

Customer_account

ID_number

This data set included information of ownership of the customers.

For example:

Customer_account 111 has 2 people in its ownership (for example: husband and wife)

Customer_account 222 has only one person on its ownership

The task is to identify customer_ID's with similar ownership.

It means that If there is at least one ID that is common in 2 or more Customer accounts then I want to put these accounts under same category.

I want to recognize it by create a new Key column that will recieve same key number of all customer accounts with similar ownership.

The calc_key field can have any number but the most important that accounts with similar ownership will have same value in this field.

What is the way to do it please?

Data Have1;
Format Date date9.;
Input Customer_ID	 Date : date9. Amount;
Cards;
111	01/10/2021	2000
222	01/10/2021	3000
222	01/12/2021	1500
111	01/12/2021	5000
333	01/10/2021	800
444	27/02/2021	2000
555	19/03/2021	2700
444	19/03/2021	300
;
Run;

Data Have2;
Input  Customer_ID	ID_number   ;
Cards;
111	39998888
111	36777333
222	36777333
333	6788888
444	9898888
555	9898888
555	827777
;
Run;

Data wanted;
format date9.;
Input Customer_ID	Date :date9.	Amount	Calc_Key;
Cards;
111	01/10/2021 2000 1
222	01/10/2021 3000 1
222	01/12/2021 1500 1
111	01/12/2021 5000 1
333	01/10/2021 800 2
444	27/02/2021 2000 2
555	19/03/2021 2700 3
444	19/03/2021 300 2

Re: Recognize accounts with similar ownership ID's

PaigeMiller — Sat, 19 Feb 2022 11:06:48 GMT

Why does 555 have calc_key=3 in the WANT data set?

Re: Recognize accounts with similar ownership ID's

Ronein — Sat, 19 Feb 2022 12:27:24 GMT

The calculated key number is just a way to recognize which accounts have common owners....I just wrote random numbers do key 3 can be any other number...

Re: Recognize accounts with similar ownership ID's

PaigeMiller — Sat, 19 Feb 2022 12:29:54 GMT

Random numbers don't help us to program a correct solution. Please change WANT to have the correct answers.

Re: Recognize accounts with similar ownership ID's

Ronein — Sat, 19 Feb 2022 12:32:09 GMT

The task is to generate key numbers ( any random numbers) with criteria that any accounts with common owners will have same key and also that the key will be unique so other accounts with different owners will have different key...

Re: Recognize accounts with similar ownership ID's

Ronein — Sat, 19 Feb 2022 12:50:38 GMT

I just wrote example to key numbers.
The key numbers can be any numbers but as I said the requirements are:
1- accounts with common owners have same key number
2- accounts with non common owners have different key

Re: Recognize accounts with similar ownership ID's

Ronein — Sat, 19 Feb 2022 13:02:17 GMT

Also possible that the key will be concatenation of accounts id's

Re: Recognize accounts with similar ownership ID's

PaigeMiller — Sat, 19 Feb 2022 14:31:19 GMT

So show us the WANT data set that you need, so we can program it and confirm that our code is giving the right answer. Don't make us figure out what is right if you already know what is right. In this situation, you have to help us out before we can help you out.

New post-Recognize accounts with similar ownership ID's

Ronein — Sat, 19 Feb 2022 16:15:00 GMT

Hello

I have the following problem:

I have 2 data sets:

1- Data set Have1 with following columns:

Customer_account , date,amount

This data set includes information on transactions that customer have in their bank account

2-Data set Have2 with following columns:

Customer_account,ID

This data set includes information on the owners' ID of each account

The task is to create a new calculated field to data set Have1 that will be called "calc_key".

This field will get the concatenation of customer accounts that have at least one common ID in their ownership.

For example:

Customer_ID 111 has two ID's: 39998888 and 36777333

Customer_ID 222 has one ID :36777333

So accounts 111 and 222 have common ID's and both will get value "111_222"

What is the way to perform this task please?

Data Have1;
format date date9.;
Input  Customer_account  date :date9. amount ;
Cards;
111	01/10/2021	2000
222	01/10/2021	3000
222	01/12/2021	1500
111	01/12/2021	5000
333	01/10/2021	800
444	27/02/2021	2000
555	19/03/2021	2700
444	19/03/2021	300
;
Run;

Data Have2;
input Customer_account  ID;
cards;
111	39998888
111	36777333
222	36777333
333	6788888
444	9898888
555	9898888
555	827777
;
Run;

Data wanted;
Input Input  Customer_account  date :date9. amount  Calc_Key $;
cards;
111	01/10/2021 2000 111_222
222	01/10/2021 3000 111_222
222	01/12/2021 1500 111_222
111	01/12/2021 5000 111_222
333	01/10/2021 800 333_444_555
444	27/02/2021 2000 333_444_555
555	19/03/2021 2700 333_444_555
444	19/03/2021 300 333_444_555
;
Run;

Re: Recognize accounts with similar ownership ID's

Ronein — Sat, 19 Feb 2022 16:26:37 GMT

I wrote it again in a new post

Data wanted;
Format date date9.;
input Customer_ID	Date :Date9.	Amount	Calc_Key $  ;
cards;
111	01/10/2021 2000 111_222
222	01/10/2021 3000 111_222
222	01/12/2021 1500 111_222
111	01/12/2021 5000 111_222
333	01/10/2021 800 333_444_555
444	27/02/2021 2000 333_444_555
555	19/03/2021 2700 333_444_555
444	19/03/2021 300 333_444_555
;
Run;

Re: Recognize accounts with similar ownership ID's

Kurt_Bremser — Sat, 19 Feb 2022 16:51:05 GMT

I moved the new thread back in here, where it belongs.