create common group id for multiple link id's

[Bharath_aavas]

Hello,

I have struck with following problem. I have two columns in the data parent id and link id. I need to link between all the id's  as shown in the output. so that I can create unique id for all those linked id's.

data have;

infile cards dlm=',';

input parent_id link_id;

cards;

9528,9534
9534,9056
8675,7890
8675,87677
7002,7001
7001,7005
7009,7005
5422,22333
78878,21121
21121,787999
787999,3422

;run;

Parent id	Link id
9528	9534
9534	9056
8675	7890
8675	87677
7002	7001
7001	7005
7009	7005
5422	22333
78878	21121
21121	787999
787999	3422

Output:

p1	p2	p3	p4	p5	p6
9528	9534	9056
7002	7001	7005	7009
8675	7890	87677
5422	22333	78878	21121	787999	3422

[FreelanceReinh]

Hello @Bharath_aavas and welcome to the SAS Support Communities!

I remember to have seen similar questions before in this forum. One example which might fit quite well and for which different solutions have been posted (including one by the famous Paul Dorfman ["hashman"]!) can be found here: https://communities.sas.com/t5/SAS-Programming/Get-ancestors-for-each-children/m-p/490584

[Bharath_aavas · Accepted solution]

I have completed the task finally and the solution is as below, it might be helpful to someone.

Thank for your rime. 

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
;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))
;
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.x,min(a.x1,b.MIN_of_x1,c.MIN_of_x1,d.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
;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))
;
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;

[KachiM]

I have followed this thread. I was thinking and thinking to get your RULE to link elements of the two columns. Your solution is very circuitous to get your logic. Can you tell your rule in getting the last 2 ROWS as in the expected output shown below? Probably your solution may be simplified using arrays.

Output:

 

p1	p2	p3	p4	p5 	p6
9528	9534	9056	 	 	 
7002	7001	7005	7009	 	 
8675	7890	87677	 	 	 
5422	22333	78878	21121	787999	3422

[Bharath_aavas]

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;