Thank you very much for your time on replying to my post. My requirement to Link of Customer IDs and create one unique id to all the linked customers. data cr_link_loan_dtl; infile cards dlm='09'x; input p1 p2; cards; 9528 9534 9534 9056 8675 7890 8675 87677 7002 7001 7001 7005 7005 7009 5422 22333 22333 78878 78878 21121 21121 787999 787999 3422 1 2 1 3 1 4 1 11 5 7 6 7 8 7 ;run; Required Output : group_id Loan_1 Loan_2 Loan_3 Loan_4 Loan_5 Loan_6 10000001 1 2 3 4 11 . 10000002 5 6 7 8 . . 10000005 3422 5422 21121 22333 78878 787999 10000006 7001 7002 7005 7009 . . 10000009 7890 8675 87677 . . . 10000010 9056 9528 9534 . . . I tried to write the code using arrays but could not succeeded. my problem is solved with that technique/logic. I know which very tedious to understand. It would great learning for me to get this done on arrays. I have inserted my SAS Code and tested and it is working in all scenarios. Yet to automate for N number of level in macro this is working until 6 relation id's.
data cr_link_loan_dtl;
infile cards dlm='09'x;
input p1 p2;
cards;
9528 9534
9534 9056
8675 7890
8675 87677
7002 7001
7001 7005
7005 7009
5422 22333
22333 78878
78878 21121
21121 787999
787999 3422
1 2
1 3
1 4
1 11
5 7
6 7
8 7
;run;
proc sort data=cr_link_loan_dtl out=link_mt_2_loans nodupkey;
by p1 p2;
run;
data link_mt_2_loans1;
set link_mt_2_loans;
if first.p1 then x=0;
x+1;
by p1;
run;
proc transpose data=link_mt_2_loans1
out=link_mt_2_loans2(drop=_name_) prefix=Loan_;
by p1;
id x;
var p2;
run;
data link_mt_2_loans3;
retain x1 10000000;
set link_mt_2_loans2;
x=N(of _all_);
x1+1;
run;
%macro test(input,output);
data _temp_data;
set &input.(keep=p1 x1 rename=(p1=loan))
&input.(keep=Loan_1 x1 rename=(loan_1=loan))
&input.(keep=Loan_2 x1 rename=(loan_2=loan))
&input.(keep=Loan_3 x1 rename=(loan_3=loan))
&input.(keep=Loan_4 x1 rename=(loan_4=loan))
;
if loan=. then delete;
run;
PROC SQL;
CREATE TABLE WORK._temp_data1 AS
SELECT t1.loan,
/* MIN_of_x1 */
(MIN(t1.x1)) AS MIN_of_x1
FROM _temp_data t1
GROUP BY t1.loan
ORDER BY MIN_of_x1;
QUIT;
proc sql;
create table &output. as
select a.p1,
a.Loan_1,
a.Loan_2,
a.Loan_3,a.Loan_4,
a.x,min(a.x1,b.MIN_of_x1,c.MIN_of_x1,d.MIN_of_x1,e.MIN_of_x1,f.MIN_of_x1
) as x1
from &input. as a
left join _temp_data1 as b on a.p1=b.loan
left join _temp_data1 as c on a.loan_1=c.loan
left join _temp_data1 as d on a.loan_2=d.loan
left join _temp_data1 as e on a.loan_3=e.loan
left join _temp_data1 as f on a.loan_4=f.loan
;quit;
%mend;
%test(link_mt_2_loans3,link_mt_2_loans3_u1);
%test(link_mt_2_loans3_u1,link_mt_2_loans3_u2);
%test(link_mt_2_loans3_u2,link_mt_2_loans3_u3);
%test(link_mt_2_loans3_u3,link_mt_2_loans3_u4);
%test(link_mt_2_loans3_u4,link_mt_2_loans3_1);
data link_mt_2_loans4_1;
set link_mt_2_loans3_1(keep=p1 x1 rename=(p1=loan))
link_mt_2_loans3_1(keep=Loan_1 x1 rename=(loan_1=loan))
link_mt_2_loans3_1(keep=Loan_2 x1 rename=(loan_2=loan))
link_mt_2_loans3_1(keep=Loan_3 x1 rename=(loan_3=loan))
link_mt_2_loans3_1(keep=Loan_4 x1 rename=(loan_4=loan))
;
if loan=. then delete;
run;
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_LINK_MT_2_LOANS4_1 AS
SELECT t1.loan,
/* MIN_of_x1 */
(MIN(t1.x1)) AS group_id
FROM WORK.LINK_MT_2_LOANS4_1 t1
GROUP BY t1.loan
ORDER BY group_id,loan;
QUIT;
data QUERY_FOR_LINK_MT_2_LOANS4_2;
set QUERY_FOR_LINK_MT_2_LOANS4_1;
if first.group_id then x=0;
x+1;
by group_id;
run;
proc transpose data=QUERY_FOR_LINK_MT_2_LOANS4_2
out=final_output(drop=_name_) prefix=Loan_;
by group_id;
id x;
var loan;
run;
... View more