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;
... View more
