Find client number

[Ronein]

Hello

I have two data sets.

In first one have fields application number and id number.

In second have fields ID number and client number.

A person (Or two people-husband and wife) arrive to company office and ask details about open a client account.

The information is in first table (application_number ID_number).

Here-

Id numbers-033948333+041876222    opened application 123

Id number 051222222 opened application  456

Id number 718888888 opened application  789

In second table have information of  all existing clients in the company  and owners of each client (Id numbers belong to the client)

Id number 033948333  belong to multiple clients:1,3

Id number 041876222  belong to multiple clients: 1,4

Id number 051222222 belong to one client:5

My task-

I want to add client number to first table using the information in second table.

I want to have criteria that all ID numbers under the application must belong to same client number.

So-

Id number 033948333 and I number 041876222 belong both to client 1

Id number belong to client 5

Id number 718888888 doesn't belong to any client (Client was not opened for him)

What is the way to create desired data set using logic?

As I said the client number should be matching to all Id numbers under the application (This is the criteria)

Data tbl1;
input application_number ID_number;
cards;
123 033948333
123 041876222
456 051222222
789 718888888
;
Run;


Data tbl2;
input ID_number client_number;
cards;
033948333 1
033948333 3
041876222 1
041876222 4
051222222 5
;
Run;



Data want;
input application_number ID_number client_number;
cards;
123 033948333 1
123 041876222 1
456 051222222 5
;
Run;

When a new person arrive to the company then open a new application and see it in first table.

When the client open then see it on second table. I want to add client number to first table using the information in second table. The problem is that a specific ID number can open multiple clients number.i want to have criteria that all ID numbers under the application must belong to same client number. I will show you the wanted Data set and my question is how to create this wanted table using logic code

[Ronein]

I found solution, maybe have other solutions?

Data tbl1;
input application_number ID_number;
cards;
123 033948333
123 041876222
456 051222222
789 718888888
;
Run;


Data tbl2;
input ID_number client_number;
cards;
033948333 1
033948333 3
041876222 1
041876222 4
051222222 5
;
Run;



/*Data want;*/
/*input application_numberID_number client_number;*/
/*cards;*/
/*123 033948333 1*/
/*123 041876222 1*/
/*456 051222222 5*/
/*;*/
/*Run;*/


/*Each Id number can beling to multiple cient numbers (tbl2)*/
/*Each application number can have multiple ID numbers (tbl1)*/
/*I want to assign one client number per application */
/*Please note that for simple- There is only one client that is fitting by this criteria*/


proc sql;
create table Help1 as
select application_number,
       count(distinct ID_number) as nr_ID_number_In_Bakasha
from tbl1
group by application_number
;
quit;


proc sql;
create table potential_lak_candidates as
select a.*,b.client_number
from tbl1 as a
inner join tbl2 as b
on a.ID_number=b.ID_number
;
quit;


proc sql;
create table Help2 as
select application_number,client_number,
              count(distinct ID_number) as n_ID_numbers_matched
from potential_lak_candidates
group by application_number,client_number
;
quit;


proc sql;
  create table Help3 as
  select a.application_number,a.client_number
  from Help2 as a
  inner join Help1 as b
   on a.application_number= b.application_number
  where a.n_ID_numbers_matched = b.nr_ID_number_In_Bakasha;
quit;


proc sql;
create table want as
select a.*,b.client_number
from tbl1 as a
left join Help3  as b
on a.application_number=b.application_number
;
quit;

  

[Ksharp]

You want this ?

Data tbl1;
input application_number ID_number;
cards;
123 033948333
123 041876222
456 051222222
789 718888888
;
Run;


Data tbl2;
input ID_number client_number;
cards;
033948333 1
033948333 3
041876222 1
041876222 4
051222222 5
;
Run;

proc sql;
create table want as
select a.*,client_number
  from tbl1 as a inner join tbl2 as b 
   on a.ID_number=b.ID_number
    group by application_number,client_number
	 having count(distinct ID_number)=
(select count(distinct ID_number) from tbl1 where application_number=a.application_number);
quit;