Creating variable that links rows from 1:2 match

[Lefty]

Hi,

I've used SAS version 9.4 to do a 2:1 match using the macro at this site. It returns data that look like this:

ID     match_1     match_2       case

1      1000024     100000022      1
2       1000024     .                      0

3       .                 100000022      0

4      100000027 100000011      1

5      100000027      .                  0  

6        .                 100000011      0  

etc

I would like to create a new variable, MatchVar, that links each set of 3 IDs (1 case, 2 controls) so I can do conditional logistic regression using the "strata" command. So I'd like my data to look like this:

ID     match_1     match_2       case        MatchVar

1      1000024     100000022      1            1
2       1000024     .                      0            1

3       .                 100000022      0            1

4      100000027 100000011      1            2

5      100000027      .                  0            2

6        .                 100000011      0           2 

etc.

Can you help me with how to code the new variable MatchVar? Thanks so much in advance.

[novinosrin]

Hi @Lefty 

You prolly need something as simple as

if case then matchvar+1;

data want;
 set have;
 if case then matchvar+1;
run;

[Lefty]

Thanks @novinosrin!

Hmm, that doesn't work, probably because my data aren't sorted the way I showed in my example-- sorry, I was trying to keep it simple. Here's a more realistic look at how my data are after I run the matching macro:

ID     match_1     match_2       case       

1      .                  100000022      0          
2       1000024    100000022      1           

.........

300    1000024           .                0           

etc.

So the first (1:1) case and control are in adjacent rows, but the second control is somewhere else in the dataset. I'm not sure how to sort the data so they're in an order that I can use code like you've written. Does that make sense?

Thanks!

---

@novinosrin wrote:

Hi @Lefty 

You prolly need something as simple as

if case then matchvar+1;

data want;
 set have;
 if case then matchvar+1;
run;

---

[novinosrin]

Withoit knowing data, it's difficult to tell nor I'm smart to have super intuitive sense to imagine the exact. Please provide more comprehensive details of your HAVE and WANT. I'm sure somebody will offer your much needed solution

[unison]

I could be guessing, but here's a way to re-sort in the way of your original post:

data have;
	input id match_1 match_2 case;
	datalines;
1 . 100000022 0
2 1000024 100000022 1
3 1000024 . 0
4 . 100000011 0
5 100000027 100000011 1
6 100000027 . 0
;
run;

data have2;
	set have;
	new_id=max((match_1 eq .)*(id+2), (match_1 ne .)*(id-1));
run;

proc sort data=have2;
	by new_id;
run;

data want;
	set have2;
	id=new_id;

	if case then
		matchvar+1;
	drop new_id;
run;

[novinosrin]

Hi @Lefty  I think I have got the idea. The trick seems to be when match_1 and matcb_2 are non missing increment and look up later individually i.e.

data have;
	input id match_1 match_2 case;
	datalines;
1 . 100000022 0
2 1000024 100000022 1
3 1000024 . 0
4 . 100000011 0
5 100000027 100000011 1
6 100000027 . 0
;
run;


data want ;
   dcl hash H () ;
   h.definekey  ("mkey") ;
   h.definedata ("match_var") ;
   h.definedone () ;
   call missing(mkey);
do until(z);
 set have end=z;
 array m match_1 match_2;
 if  match_1>. and match_2>. or case then do;
  match_var+1;
  do over m;
  h.add(key:m,data:match_var);
  end;
 end;
end; 
z=0;
do until(z);
 set have end=z;
 do over m;
  if m>. then h.find(key:m);
 end;
 output;
end;
stop;
drop mkey;
run;

[Lefty]

Thanks! When I run your code (with my dataset name swapped out for have), I get the error "ERROR: Duplicate key." a bunch of times. I'm not familiar enough with what this code is doing to figure out what I need to change. Do you see anything wrong? Thanks!

[novinosrin]

Hi @Lefty  Do not worry about the code/syntax and concept. We are here to help you.  

"ERROR: Duplicate key."

means the HASH table has been set with a constraint to have only unique keys, and that was on purpose. But now that my assumption is wrong, I would appreciate if you could post us a more comprehensive sample. 

Example, Let me modify your sample from obs1-3 by copying the 1-3 and appending as 4-6

1 . 100000022 0
2 1000024 100000022 1
3 1000024 . 0
4 . 100000022 0
5 1000024 100000022 1
6 1000024 . 0

So here 4-6 is a copy paste of 1-3. So the case combination 1000024 100000022 1 has occurred twice making it a duplicate that wasn't compliant to the constraint set. Not a problem. However, before I modify the code, I would rather like to see to more representative sample. Also, would the above 6 be tagged to the same match_var?

I know it can go back and forth, I'd appreciate your time. Thanks!

[Lefty]

---

Thank you so much @novinosrin! I think my attempts to simplify actually made it much LESS clear and I am sorry!
I'm attaching my dataset so hopefully it's a little easier to understand.
ID=each person's unique ID (note that unlike the example I typed in the beginning, it is not in consecutive order, it's just a random, unique number)
case: if this=1, it means that ID matched to two other IDs for whom case=0. My goal is to give that triplet the same value of some new variable (matchvar) as each other. The way to find the two controls that each case matched to is by using the match_1 and match_2 variables. For example, ID 29759649502 has a value of 10000032 for match_1. Its corresponding matched control pair is way down the spreadsheet at ID 28477027301, who also has a value of 10000032 for match_1. ID 29759649502's value for match_2 is 10000033, so its second matched control pair is ID 2490602401 which also has a value of 10000033 for match_2.

I tried to paste a bit of my spreadsheet below and bold the relevant rows. Does that help? I am so appreciative of your help!!

ID	case	match_1	match_2
1953457302	0		10000019
1640479605	1	10000019	10000019
2256689601	0		10000021
25051280101	1	10000021	10000021
2490602401	0		10000033
29759649502	1	10000032	10000033

.....

ID

case

match_1

match_2

28477027301

0

10000032

.

......

Hi @Lefty  Do not worry about the code/syntax and concept. We are here to help you.  

"ERROR: Duplicate key."

means the HASH table has been set with a constraint to have only unique keys, and that was on purpose. But now that my assumption is wrong, I would appreciate if you could post us a more comprehensive sample. 

Example, Let me modify your sample from obs1-3 by copying the 1-3 and appending as 4-6

1 . 100000022 0
2 1000024 100000022 1
3 1000024 . 0
4 . 100000022 0
5 1000024 100000022 1
6 1000024 . 0

So here 4-6 is a copy paste of 1-3. So the case combination 1000024 100000022 1 has occurred twice making it a duplicate that wasn't compliant to the constraint set. Not a problem. However, before I modify the code, I would rather like to see to more representative sample. Also, would the above 6 be tagged to the same match_var?

I know it can go back and forth, I'd appreciate your time. Thanks!

---

---

@novinosrin wrote:

Hi @Lefty  Do not worry about the code/syntax and concept. We are here to help you.  

"ERROR: Duplicate key."

means the HASH table has been set with a constraint to have only unique keys, and that was on purpose. But now that my assumption is wrong, I would appreciate if you could post us a more comprehensive sample. 

Example, Let me modify your sample from obs1-3 by copying the 1-3 and appending as 4-6

1 . 100000022 0
2 1000024 100000022 1
3 1000024 . 0
4 . 100000022 0
5 1000024 100000022 1
6 1000024 . 0

So here 4-6 is a copy paste of 1-3. So the case combination 1000024 100000022 1 has occurred twice making it a duplicate that wasn't compliant to the constraint set. Not a problem. However, before I modify the code, I would rather like to see to more representative sample. Also, would the above 6 be tagged to the same match_var?

I know it can go back and forth, I'd appreciate your time. Thanks!

---

@novinosrin wrote:

Hi @Lefty  Do not worry about the code/syntax and concept. We are here to help you.  

"ERROR: Duplicate key."

means the HASH table has been set with a constraint to have only unique keys, and that was on purpose. But now that my assumption is wrong, I would appreciate if you could post us a more comprehensive sample. 

Example, Let me modify your sample from obs1-3 by copying the 1-3 and appending as 4-6

1 . 100000022 0
2 1000024 100000022 1
3 1000024 . 0
4 . 100000022 0
5 1000024 100000022 1
6 1000024 . 0

So here 4-6 is a copy paste of 1-3. So the case combination 1000024 100000022 1 has occurred twice making it a duplicate that wasn't compliant to the constraint set. Not a problem. However, before I modify the code, I would rather like to see to more representative sample. Also, would the above 6 be tagged to the same match_var?

I know it can go back and forth, I'd appreciate your time. Thanks!

---

@novinosrin wrote:

Hi @Lefty  Do not worry about the code/syntax and concept. We are here to help you.  

"ERROR: Duplicate key."

means the HASH table has been set with a constraint to have only unique keys, and that was on purpose. But now that my assumption is wrong, I would appreciate if you could post us a more comprehensive sample. 

Example, Let me modify your sample from obs1-3 by copying the 1-3 and appending as 4-6

1 . 100000022 0
2 1000024 100000022 1
3 1000024 . 0
4 . 100000022 0
5 1000024 100000022 1
6 1000024 . 0

So here 4-6 is a copy paste of 1-3. So the case combination 1000024 100000022 1 has occurred twice making it a duplicate that wasn't compliant to the constraint set. Not a problem. However, before I modify the code, I would rather like to see to more representative sample. Also, would the above 6 be tagged to the same match_var?

I know it can go back and forth, I'd appreciate your time. Thanks!

---

[novinosrin]

Hi again @Lefty   Try the below and let me know. 

data have;
infile cards expandtabs truncover;
input ID	case	match_1	match_2;
cards;
1953457302	0	.	10000019
1640479605	1	10000019	10000019
2256689601	0	.	10000021
25051280101	1	10000021	10000021
2490602401	0	.	10000033
29759649502	1	10000032	10000033
28327765601	0	.	10000044
26419706701	1	1000044	10000044
1151870302	0	.	10000056
27919709901	1	10000052	10000056
657227901	0	.	10000061
1532605102	1	10000057	10000061
1540305301	0	.	10000072
602068803	1	10000068	10000072
25483567401	0	.	10000078
28760760901	1	10000074	10000078
28666323302	0	.	10000088
510517201	1	10000084	10000088
26622928101	0	.	10000090
1202119202	1	10000086	10000090
2834897901	0	.	100000001
26208854002	1	100000001	100000001
30304697502	0	.	100000006
2297937201	1	100000006	100000006
2106416002	0	.	100000008
26809935402	1	100000008	100000008
2308985502	0	.	100000009
27470861602	1	10000009	100000009
3133238601	0	.	100000010
3173987702	1	10000010	100000010
3468107402	0	.	100000014
26055289401	1	100000012	100000014
3371691701	0	.	100000015
31361390401	1	100000013	100000015
26062439802	0	.	100000017
1794800801	1	100000015	100000017
31812144401	0	.	100000018
28425000401	1	100000016	100000018
31917124402	0	.	100000019
27057131002	1	100000017	100000019
1323874601	0	.	100000020
25043540701	1	10000022	100000020
27520093702	0	.	100000021
28182057101	1	100000018	100000021
26701790801	0	.	100000022
27112743002	1	1000024	100000022
27431434002	0	.	100000023
28179718501	1	100000019	100000023
28438159401	0	.	100000024
3339937301	1	100000020	100000024
26229681701	0	.	100000025
1549255201	1	100000021	100000025
25042363201	0	.	100000027
28079888701	1	100000022	100000027
2110198701	0	.	100000028
29753238401	1	10000029	100000028
28630416001	0	.	100000029
1534740802	1	100000023	100000029
25076201301	0	.	100000030
27190293001	1	100000024	100000030
30866877502	0	.	100000031
25058565202	1	100000025	100000031
28282898102	0	.	100000032
1025477202	1	100000026	100000032
2253534002	0	.	100000033
26462234702	1	10000035	100000033
3157148702	0	.	100000035
28293743501	1	100000028	100000035
1941007102	0	.	100000036
152951101	1	100000029	100000036
26395169802	0	.	100000037
28258391902	1	10000039	100000037
842980501	0	.	100000038
28050916201	1	100000030	100000038
29351756501	0	.	100000039
30862290102	1	100000031	100000039
28152765901	0	.	100000040
27537636802	1	10000042	100000040
26350688501	0	.	100000042
2999192001	1	1000046	100000042
1326581802	0	.	100000044
2556080701	1	100000034	100000044
29679891401	0	.	100000045
29453068601	1	10000046	100000045
715936302	0	.	100000046
31949449302	1	100000035	100000046
3087952801	0	.	100000048
1815719602	1	10000049	100000048
1958690001	0	.	100000049
28746654001	1	1000053	100000049
26602733401	0	.	100000050
2264165601	1	10000050	100000050
2074387002	0	.	100000051
26571742302	1	100000037	100000051
29359558001	0	.	100000053
26429426504	1	100000039	100000053
26562657601	0	.	100000054
26119245802	1	10000055	100000054
30799571301	0	.	100000055
26201411701	1	100000040	100000055
1465434802	0	.	100000056
1784529401	1	100000041	100000056
640081003	0	.	100000057
3140761201	1	100000042	100000057
3230532701	0	.	100000058
32449463701	1	100000043	100000058
25927353701	0	.	100000059
27632035401	1	100000044	100000059
877898604	0	.	100000060
28402184402	1	100000045	100000060
3369466401	0	.	100000061
1498573801	1	100000046	100000061
28654919402	0	.	100000063
28381878502	1	100000048	100000063
1310935202	0	.	100000064
25913787201	1	100000049	100000064
3416885201	0	.	100000065
25081064401	1	100000050	100000065
28093678701	0	.	100000066
28667436401	1	10000069	100000066
3040934802	0	.	100000067
2467683701	1	100000051	100000067
26430233001	0	.	100000068
26530397701	1	10000071	100000068
1379053101	0	.	100000070
768704701	1	100000052	100000070
26846345201	0	.	100000071
864972102	1	10000075	100000071
2211165603	0	.	100000072
1332829002	1	100000053	100000072
25440907302	0	.	100000073
1398313302	1	10000077	100000073
29456099101	0	.	100000074
28606999701	1	100000054	100000074
2817807701	0	.	100000075
25037539101	1	10000079	100000075
27473157301	0	.	100000076
54897502	1	10000080	100000076
2241874501	0	.	100000077
2490532805	1	100000055	100000077
26293185301	0	.	100000078
28420484702	1	10000082	100000078
2437598202	0	.	100000080
2238124402	1	10000085	100000080
606199702	0	.	100000081
27048034001	1	100000057	100000081
28905064002	0	.	100000082
25241786502	1	100000058	100000082
1277272902	0	.	100000083
25490363802	1	100000059	100000083
25945835601	0	.	100000085
1327838602	1	100000061	100000085
562574202	0	.	100000086
28684716701	1	100000062	100000086
1446504102	0	.	100000091
703646401	1	10000097	100000091
29477908102	0	.	100000100
26680402801	1	100000074	100000100
197332403	0	.	100000102
2112731802	1	100000076	100000102
28875556302	0	.	100000103
27615014701	1	100000077	100000103
1451579701	0	.	100000104
2429453201	1	100000078	100000104
28298471802	0	.	100000105
27363837301	1	100000079	100000105
2916190802	0	.	100000113
2888297302	1	10000119	100000113
26403080901	0	.	100000114
1554927401	1	100000087	100000114
28729765401	0	.	100000115
27492848001	1	100000088	100000115
27568480702	0	.	100000116
158039402	1	100000089	100000116
609666201	0	.	100000117
1281856402	1	10000123	100000117
2501802501	0	.	100000118
692939101	1	100000090	100000118
28677970201	0	.	100000119
30882057501	1	100000091	100000119
28448913302	0	.	100000122
105577201	1	10000128	100000122
28364155601	0	.	100000126
31810495301	1	100000097	100000126
1586113401	0	.	100000127
28575146201	1	100000098	100000127
27569930802	0	.	100000130
25059615301	1	100000101	100000130
606396002	0	.	100000131
25486622402	1	100000102	100000131
134320301	0	.	100000134
2074541302	1	100000104	100000134
25987610103	0	.	100000138
1965857001	1	100000108	100000138
26828146402	0	.	100000139
2494784501	1	100000109	100000139
1085034001	0	.	100000141
768270702	1	100000111	100000141
27231315201	0	.	100000142
1786721701	1	10000148	100000142
1266217503	0	.	100000144
28827599902	1	100000113	100000144
1091395901	0	.	100000156
2388237701	1	100000125	100000156
32153619601	0	.	100000161
28208931301	1	100000130	100000161
26409110501	0	.	100000166
2553424801	1	100000135	100000166
30816275501	0	.	100000168
635287902	1	100000137	100000168
26480255001	0	.	100000169
605072602	1	100000138	100000169
1955351702	0	.	100000170
1863347202	1	100000139	100000170
27100625301	0	.	100000183
28331459301	1	100000152	100000183
25936706001	0	.	100000186
31055314602	1	100000155	100000186
1175692603	0	.	100000190
29634688501	1	10000196	100000190
26740735702	0	.	100000205
921743101	1	100000171	100000205
598648002	0	.	100000211
31969334501	1	10000217	100000211
32004882202	0	.	100000215
701849301	1	100000179	100000215
3150873801	0	.	100000218
1826839602	1	100000182	100000218
26426264001	0	.	100000236
1006099502	1	10000241	100000236
26536528502	0	.	100000239
28337890302	1	10000244	100000239
27365996101	0	.	100000241
26411786601	1	10000246	100000241
26985493201	0	.	100000244
2139058602	1	100000203	100000244
25980719401	0	.	100000273
669374202	1	10000278	100000273
31362056301	0	.	1000000001
3038283702	1	100000002	1000000001
27043257501	0	.	1000000002
2296665903	1	100000003	1000000002
1892105303	0	.	1000000003
25489833302	1	1000000001	1000000003
3156066601	0	.	1000000004
725302001	1	100000005	1000000004
1255490002	0	.	1000000005
30098222901	1	100000007	1000000005
31359753201	0	.	1000000006
1966985702	1	100000009	1000000006
28290800101	0	.	1000000007
25380255401	1	100000010	1000000007
2096862005	0	.	1000000008
28749731101	1	100000011	1000000008
25263904504	0	.	1000000009
30103102402	1	100000014	1000000009
31949993301	0	.	1000000010
1816116401	1	10000027	1000000010
2464797802	0	.	1000000011
28642908101	1	100000027	1000000011
25047988001	0	.	1000000012
25047837901	1	1000000002	1000000012
32170320701	0	.	1000000013
740707001	1	100000033	1000000013
32035249702	0	.	1000000014
26964666501	1	100000036	1000000014
748566101	0	.	1000000015
131439901	1	100000038	1000000015
1542144601	0	.	1000000016
587239002	1	100000047	1000000016
26507710601	0	.	1000000017
26427415201	1	10000072	1000000017
1560814402	0	.	1000000018
26705102901	1	1000000003	1000000018
662761001	0	.	1000000019
27406402401	1	100000060	1000000019
28432744001	0	.	1000000020
28465194301	1	1000000004	1000000020
28709432301	0	.	1000000021
27556124001	1	10000094	1000000021
1060494801	0	.	1000000022
25023142402	1	1000000005	1000000022
26918555501	0	.	1000000023
25165458501	1	100000065	1000000023
98507001	0	.	1000000024
25075435302	1	100000066	1000000024
27342111601	0	.	1000000025
1556764102	1	100000067	1000000025
1332206501	0	.	1000000026
3203217401	1	100000068	1000000026
28670647801	0	.	1000000027
1547165102	1	100000069	1000000027
26679909001	0	.	1000000028
26050686802	1	1000000006	1000000028
28620621602	0	.	1000000029
25031434701	1	100000071	1000000029
1966856701	0	.	1000000030
1333899004	1	1000000007	1000000030
26897878401	0	.	1000000031
27294547101	1	100000073	1000000031
2569851202	0	.	1000000032
1065789302	1	1000000008	1000000032
28725210701	0	.	1000000033
25047759201	1	100000080	1000000033
1279612401	0	.	1000000034
29212310301	1	100000081	1000000034
266387801	0	.	1000000035
760559601	1	1000000009	1000000035
28893321302	0	.	1000000036
28323451802	1	100000083	1000000036
28649550202	0	.	1000000037
177197606	1	1000000010	1000000037
27507971701	0	.	1000000038
30495365501	1	1000000011	1000000038
2074641002	0	.	1000000039
25072970901	1	100000086	1000000039
28652996602	0	.	1000000040
25501612601	1	100000092	1000000040
27547984301	0	.	1000000041
27348758901	1	1000000012	1000000041
1543798401	0	.	1000000042
25068488001	1	100000094	1000000042
131127801	0	.	1000000043
1541422102	1	1000000013	1000000043
25042745601	0	.	1000000044
1360247801	1	100000096	1000000044
1903908102	0	.	1000000045
1354795701	1	1000000014	1000000045
27055884802	0	.	1000000046
25056701102	1	100000100	1000000046
26361870301	0	.	1000000047
28129443002	1	10000138	1000000047
28335618401	0	.	1000000048
28255939201	1	1000000015	1000000048
2298762001	0	.	1000000049
26057632101	1	1000000016	1000000049
27820768101	0	.	1000000050
27337599701	1	100000106	1000000050
28365737401	0	.	1000000051
1192766302	1	100000107	1000000051
663568601	0	.	1000000052
29401362202	1	100000110	1000000052
2569908901	0	.	1000000053
1576421301	1	1000000017	1000000053
28568093902	0	.	1000000054
1529090301	1	100000114	1000000054
28115009602	0	.	1000000055
26964809901	1	100000115	1000000055
609576802	0	.	1000000056
26508305201	1	100000116	1000000056
26918729502	0	.	1000000057
161521301	1	100000117	1000000057
627917002	0	.	1000000058
222550502	1	1000000018	1000000058
29262802501	0	.	1000000059
1314265401	1	1000000019	1000000059
2214860702	0	.	1000000060
1453817401	1	100000120	1000000060
927856201	0	.	1000000061
2131474801	1	100000121	1000000061
26670313901	0	.	1000000062
28715574202	1	100000122	1000000062
892517302	0	.	1000000063
706085501	1	1000000020	1000000063
28577047002	0	.	1000000064
28131747102	1	100000124	1000000064
28007901601	0	.	1000000065
25929252801	1	100000126	1000000065
2144991301	0	.	1000000066
1106789601	1	1000000021	1000000066
27043521701	0	.	1000000067
28256214601	1	1000000022	1000000067
29466357502	0	.	1000000068
1911071701	1	1000000023	1000000068
137421601	0	.	1000000069
27251513801	1	1000000024	1000000069
27538396101	0	.	1000000070
108393401	1	1000000025	1000000070
27562742801	0	.	1000000071
225131101	1	1000000026	1000000071
27569863602	0	.	1000000072
28606863601	1	100000134	1000000072
27570872302	0	.	1000000073
25404272702	1	1000000027	1000000073
26541393501	0	.	1000000074
25925900501	1	1000000028	1000000074
767614502	0	.	1000000075
1336570002	1	1000000029	1000000075
110930101	0	.	1000000080
2478620702	1	1000000033	1000000080
28842401801	0	.	1000000081
240315901	1	100000147	1000000081
26622833902	0	.	1000000082
27354160701	1	1000000034	1000000082
1717768603	0	.	1000000083
221069001	1	1000000035	1000000083
1146403602	0	.	1000000084
744819702	1	1000000036	1000000084
2671633902	0	.	1000000085
617560601	1	1000000037	1000000085
1413680201	0	.	1000000086
590739403	1	1000000038	1000000086
1720952301	0	.	1000000087
28715042201	1	100000154	1000000087
28575551501	0	.	1000000088
26663080401	1	1000000039	1000000088
145679801	0	.	1000000089
2433524402	1	100000157	1000000089
25931486601	0	.	1000000090
28574636201	1	10000195	1000000090
26341366901	0	.	1000000091
29255640602	1	100000158	1000000091
28238113601	0	.	1000000092
1262062701	1	100000159	1000000092
132922601	0	.	1000000093
1537799202	1	1000000040	1000000093
28106122202	0	.	1000000094
25354597502	1	100000161	1000000094
642895801	0	.	1000000095
29375241901	1	10000201	1000000095
552643702	0	.	1000000096
1904043701	1	100000162	1000000096
906591803	0	.	1000000097
25065618402	1	1000000041	1000000097
27383418901	0	.	1000000098
1178565301	1	100000164	1000000098
891638701	0	.	1000000099
1821369401	1	100000165	1000000099
614778901	0	.	1000000100
3142431001	1	100000166	1000000100
2148221301	0	.	1000000104
26965236201	1	1000000044	1000000104
3133721601	0	.	1000000105
523081201	1	1000000045	1000000105
588278402	0	.	1000000106
763983802	1	100000173	1000000106
26976947801	0	.	1000000107
2614622302	1	100000174	1000000107
1281105102	0	.	1000000110
903400304	1	1000000047	1000000110
1270197101	0	.	1000000111
28702008801	1	100000177	1000000111
1941245901	0	.	1000000112
25253238802	1	1000000048	1000000112
31123934701	0	.	1000000113
890496902	1	100000180	1000000113
1482030501	0	.	1000000114
1281319401	1	100000181	1000000114
1482728701	0	.	1000000115
32548474601	1	1000000049	1000000115
28422187602	0	.	1000000116
27390359201	1	1000000050	1000000116
892104602	0	.	1000000117
1515082601	1	100000185	1000000117
2315862101	0	.	1000000118
26346333401	1	1000000051	1000000118
27647454102	0	.	1000000119
1263344601	1	100000187	1000000119
25380837801	0	.	1000000120
2848532602	1	100000188	1000000120
27315730301	0	.	1000000121
1333984801	1	1000000052	1000000121
31796747901	0	.	1000000122
26852101001	1	100000190	1000000122
27474069101	0	.	1000000123
1468564801	1	1000000053	1000000123
28574333901	0	.	1000000124
28565748002	1	1000000054	1000000124
892765702	0	.	1000000125
28618668302	1	1000239	1000000125
28466224702	0	.	1000000126
202408601	1	1000000055	1000000126
1385931502	0	.	1000000127
1292941001	1	1000000056	1000000127
1947739101	0	.	1000000128
1576260002	1	100000195	1000000128
28234374602	0	.	1000000129
28094500901	1	1000000057	1000000129
27299020401	0	.	1000000130
29673753802	1	1000000058	1000000130
25151348701	0	.	1000000131
3366315801	1	100000198	1000000131
967836002	0	.	1000000134
26927891901	1	1000000061	1000000134
1280209001	0	.	1000000137
27090276301	1	1000000063	1000000137
28519084801	0	.	1000000138
509369702	1	100000205	1000000138
3158895802	0	.	1000000139
131306501	1	1000000064	1000000139
49647702	0	.	1000000141
766838302	1	1000000066	1000000141
25038463301	0	.	1000000142
599691001	1	1000000067	1000000142
605684602	0	.	1000000144
2413372901	1	1000000069	1000000144
1575955501	0	.	1000000149
28646022201	1	1000000074	1000000149
25063761202	0	.	1000000150
1659487701	1	10000263	1000000150
729118801	0	.	1000000153
2477447601	1	1000000077	1000000153
27339592701	0	.	1000000154
27399328701	1	1000000078	1000000154
26690406201	0	.	1000000155
27525687702	1	100000221	1000000155
608796801	0	.	1000000156
1483717002	1	100000222	1000000156
311589401	0	.	1000000157
148281901	1	1000000079	1000000157
1738639902	0	.	1000000158
25080020702	1	1000000080	1000000158
29426636701	0	.	1000000159
904186702	1	1000000081	1000000159
26809192601	0	.	1000000162
28290281202	1	1000000084	1000000162
2765518701	0	.	1000000165
28369718201	1	1000000087	1000000165
1776723401	0	.	1000000166
28785254101	1	1000000088	1000000166
904832701	0	.	1000000167
1953568002	1	1000000089	1000000167
25101584502	0	.	1000000168
1129905901	1	100000234	1000000168
2234636701	0	.	1000000169
25080907301	1	1000000090	1000000169
1949063002	0	.	1000000172
32152428001	1	1000000093	1000000172
25069232602	0	.	1000000174
26968446501	1	1000000094	1000000174
1526965102	0	.	1000000175
25028866302	1	100000241	1000000175
1463942401	0	.	1000000180
1262671502	1	1000000099	1000000180
26789884201	0	.	1000000181
26363956702	1	1000000100	1000000181
26978898402	0	.	1000000183
31376140501	1	1000000102	1000000183
99516001	0	.	1000000185
27043656502	1	1000000104	1000000185
28664240702	0	.	1000000188
28612182401	1	1000000107	1000000188
28601309101	0	.	1000000195
25244726701	1	1000000113	1000000195
97793102	0	.	1000000198
25043608501	1	1000000114	1000000198
29503822602	0	.	10000000001
1149219701	1	1000000030	10000000001
1404137602	0	.	10000000002
1657138104	1	1000000031	10000000002
28291193001	0	.	10000000003
2528699501	1	1000000032	10000000003
2296644702	0	.	10000000004
136329101	1	100000145	10000000004
27493108202	0	.	10000000005
1664293502	1	1000000042	10000000005
3204412902	0	.	10000000006
1628998802	1	100000168	10000000006
1842366201	0	.	10000000007
26808324501	1	1000000043	10000000007
601655002	0	.	10000000008
1630056701	1	10000215	10000000008
2146372802	0	.	10000000009
28561059001	1	1000000046	10000000009
25978773801	0	.	10000000010
1264200601	1	1000000059	10000000010
1661644701	0	.	10000000011
27505115301	1	1000000060	10000000011
221064502	0	.	10000000012
28921855602	1	10000247	10000000012
26376080402	0	.	10000000013
740985901	1	1000000062	10000000013
26818922601	0	.	10000000014
1538766801	1	10000000001	10000000014
1542729701	0	.	10000000015
1781982202	1	1000000068	10000000015
28075816501	0	.	10000000016
1899885101	1	1000000070	10000000016
1904191202	0	.	10000000017
2044875502	1	1000000071	10000000017
1546472902	0	.	10000000018
28581446901	1	10000000002	10000000018
893099102	0	.	10000000019
1050446801	1	10000000003	10000000019
124385501	0	.	10000000020
28762652301	1	1000000075	10000000020
694801601	0	.	10000000021
28500326601	1	1000000076	10000000021
905360502	0	.	10000000022
2234935802	1	1000000082	10000000022
659159001	0	.	10000000023
26463936501	1	1000000083	10000000023
1400015301	0	.	10000000024
977516202	1	1000000085	10000000024
745788901	0	.	10000000025
32616054002	1	1000000086	10000000025
54501201	0	.	10000000026
1261807902	1	1000000091	10000000026
26445053401	0	.	10000000027
221909001	1	1000000092	10000000027
1570713202	0	.	10000000028
1841106102	1	100000239	10000000028
26229525301	0	.	10000000029
28916826001	1	1000000095	10000000029
26390332501	0	.	10000000030
2122156901	1	1000000096	10000000030
28686992801	0	.	10000000031
1278075701	1	10000000004	10000000031
1395836001	0	.	10000000032
1217987201	1	10000000005	10000000032
26791396402	0	.	10000000033
219153301	1	1000000101	10000000033
511962401	0	.	10000000034
214430204	1	1000000103	10000000034
842215501	0	.	10000000035
1261163601	1	1000000105	10000000035
603517201	0	.	10000000036
1521377101	1	10000000006	10000000036
28254931701	0	.	10000000037
28436508902	1	1000000108	10000000037
104340901	0	.	10000000038
26411694001	1	1000000109	10000000038
596826001	0	.	10000000039
26959345502	1	10000000007	10000000039
1903911501	0	.	10000000040
1772318002	1	10000306	10000000040
1260556002	0	.	10000000041
27820656102	1	1000000111	10000000041
1536164502	0	.	10000000042
28167497801	1	1000000112	10000000042
3169257001	0	.	10000000043
318517002	1	100000261	10000000043
25179651701	0	.	10000000044
114506202	1	100000262	10000000044
28609221801	0	.	10000000045
620041802	1	10000000008	10000000045
27080550002	0	.	10000000046
25052843301	1	10000000009	10000000046
28851796101	0	.	10000000047
1828343801	1	10000000010	10000000047
103588401	0	.	10000000051
1513384202	1	1000000121	10000000051
1847391801	0	.	10000000052
891918301	1	1000000122	10000000052
1849684102	0	.	10000000053
671941602	1	10000000011	10000000053
891622102	0	.	10000000054
2494252701	1	1000000124	10000000054
1014340401	0	.	10000000055
2155337802	1	10000000012	10000000055
25263379201	0	.	10000000056
599395201	1	10000000013	10000000056
29376053601	0	.	10000000057
29416529202	1	10000000014	10000000057
28260419902	0	.	10000000058
622766702	1	10000000015	10000000058
692707102	0	.	10000000059
28010865201	1	100000278	10000000059
30094906101	0	.	10000000060
597934101	1	1000000129	10000000060
26203067301	0	.	10000000061
27692857102	1	1000000130	10000000061
1331227801	0	.	10000000062
1333124802	1	1000000131	10000000062
28098188002	0	.	10000000063
589685401	1	10000000016	10000000063
27565373301	0	.	10000000064
683099502	1	10000000017	10000000064
26541422201	0	.	10000000069
1331127201	1	10000000022	10000000069
1656233202	0	.	10000000070
26704106601	1	1000000139	10000000070
26355443001	0	.	10000000075
2261424002	1	10000000024	10000000075
1843906002	0	.	10000000076
598665601	1	1000000145	10000000076
147895501	0	.	10000000079
28168078702	1	10000000027	10000000079
894471502	0	.	10000000085
1288948203	1	10000000030	10000000085
1294484201	0	.	10000000086
1265611201	1	1000000154	10000000086
591187601	0	.	10000000087
25385032201	1	10000000031	10000000087
26523467301	0	.	10000000094
28201972001	1	10000000041	10000000094
2022905101	0	.	10000000095
3153220402	1	100000319	10000000095
1557829602	0	.	10000000096
319456301	1	10000000042	10000000096
28280987802	0	.	10000000101
27563251402	1	10000000049	10000000101
158689001	0	.	10000000102
1262006902	1	1000000177	10000000102
1055475301	0	1000024	.
2140739401	0	1000044	.
879248701	0	1000046	.
1014196001	0	1000053	.
31915510402	0	1000239	.
739161802	0	10000009	.
3358474701	0	10000010	.
25047797901	0	10000019	.
1983583001	0	10000021	.
105221502	0	10000022	.
25487746901	0	10000027	.
27450491502	0	10000029	.
28477027301	0	10000032	.
2673387103	0	10000035	.
27043724701	0	10000039	.
26075364702	0	10000042	.
25056471601	0	10000046	.
739823601	0	10000049	.
879205401	0	10000050	.
25028378801	0	10000052	.
28273147001	0	10000055	.
1260606101	0	10000057	.
2222362302	0	10000068	.
1519219902	0	10000069	.
1014104301	0	10000071	.
25076003801	0	10000072	.
157736601	0	10000074	.
26847471901	0	10000075	.
2142807201	0	10000077	.
855432501	0	10000079	.
27041395902	0	10000080	.
2106612902	0	10000082	.
29479145901	0	10000084	.
26847040601	0	10000085	.
26441879701	0	10000086	.
28749569901	0	10000094	.
29024301	0	10000097	.
26766262201	0	10000119	.
27006176001	0	10000123	.
626345002	0	10000128	.
875944001	0	10000138	.
241621302	0	10000148	.
26851351002	0	10000195	.
27563768301	0	10000196	.
620680903	0	10000201	.
2491211901	0	10000215	.
27635316202	0	10000217	.
25076550102	0	10000241	.
28808553801	0	10000244	.
27567518301	0	10000246	.
717477501	0	10000247	.
28567010601	0	10000263	.
25980992201	0	10000278	.
28879271102	0	10000306	.
709183704	0	100000001	.
26201139602	0	100000002	.
1127354401	0	100000003	.
1413888001	0	100000005	.
29789990701	0	100000006	.
1976344501	0	100000007	.
2217820702	0	100000008	.
3148742501	0	100000009	.
2179119201	0	100000010	.
3148226401	0	100000011	.
27607546801	0	100000012	.
1024738302	0	100000013	.
26941152402	0	100000014	.
26428369001	0	100000015	.
1284883301	0	100000016	.
2229057402	0	100000017	.
31915614502	0	100000018	.
1422212601	0	100000019	.
3268400601	0	100000020	.
2280537501	0	100000021	.
25056445102	0	100000022	.
1603515301	0	100000023	.
200367001	0	100000024	.
28654518402	0	100000025	.
28592742902	0	100000026	.
28702598001	0	100000027	.
26711105801	0	100000028	.
1535434102	0	100000029	.
26234972802	0	100000030	.
32006041902	0	100000031	.
25042647401	0	100000033	.
26428113201	0	100000034	.
28290781502	0	100000035	.
27653768102	0	100000036	.
27050855901	0	100000037	.
27504761202	0	100000038	.
26808990002	0	100000039	.
27574651101	0	100000040	.
1788202402	0	100000041	.
27548082101	0	100000042	.
25064328401	0	100000043	.
27246147202	0	100000044	.
29448962601	0	100000045	.
25079380401	0	100000046	.
26415210701	0	100000047	.
1503748501	0	100000048	.
28357398901	0	100000049	.
28088085202	0	100000050	.
28698857701	0	100000051	.
676500702	0	100000052	.
26727745702	0	100000053	.
172050601	0	100000054	.
904632001	0	100000055	.
1473206102	0	100000057	.
26785413702	0	100000058	.
28339589002	0	100000059	.
3060031001	0	100000060	.
1567329101	0	100000061	.
28597519102	0	100000062	.
25149498107	0	100000065	.
1062897301	0	100000066	.
733287402	0	100000067	.
3090192001	0	100000068	.
3130047002	0	100000069	.
28492205502	0	100000071	.
3083308701	0	100000073	.
26159942601	0	100000074	.
26842296502	0	100000076	.
29133805301	0	100000077	.
25180920701	0	100000078	.
1064196901	0	100000079	.
864234501	0	100000080	.
553641901	0	100000081	.
29120001	0	100000083	.
28348604102	0	100000086	.
28337312302	0	100000087	.
26670702001	0	100000088	.
1746511801	0	100000089	.
3484191102	0	100000090	.
1954925401	0	100000091	.
1231510102	0	100000092	.
1175525802	0	100000094	.
25965367702	0	100000096	.
28292990201	0	100000097	.
2497975101	0	100000098	.
27561349502	0	100000100	.
28683427601	0	100000101	.
558012302	0	100000102	.
26505894901	0	100000104	.
2079403002	0	100000106	.
26624433502	0	100000107	.
1376244401	0	100000108	.
28405814001	0	100000109	.
1227189302	0	100000110	.
1845888801	0	100000111	.
29460850001	0	100000113	.
589353401	0	100000114	.
619587101	0	100000115	.
27617181601	0	100000116	.
28176644402	0	100000117	.
25136333401	0	100000120	.
1482192802	0	100000121	.
2246292701	0	100000122	.
716711601	0	100000124	.
135905301	0	100000125	.
26032054501	0	100000126	.
26642769501	0	100000130	.
147888001	0	100000134	.
28119351802	0	100000135	.
889416903	0	100000137	.
26991502801	0	100000138	.
26972542601	0	100000139	.
621528103	0	100000145	.
1066911702	0	100000147	.
31942725602	0	100000152	.
26210353301	0	100000154	.
2272162902	0	100000155	.
28752595401	0	100000157	.
369019401	0	100000158	.
694526601	0	100000159	.
26411185701	0	100000161	.
2217252402	0	100000162	.
1267293402	0	100000164	.
894086202	0	100000165	.
1012786801	0	100000166	.
512169802	0	100000168	.
27497821202	0	100000171	.
26963947601	0	100000173	.
1348058301	0	100000174	.
622508402	0	100000177	.
28409882001	0	100000179	.
28281832301	0	100000180	.
27545455301	0	100000181	.
2036961502	0	100000182	.
1528957301	0	100000185	.
26288647701	0	100000187	.
2025845002	0	100000188	.
692917901	0	100000190	.
692872802	0	100000195	.
1258370802	0	100000198	.
26106544601	0	100000203	.
27223612401	0	100000205	.
595743202	0	100000221	.
26812264501	0	100000222	.
1917878202	0	100000234	.
538320201	0	100000239	.
25243958301	0	100000241	.
1553366402	0	100000261	.
28103611301	0	100000262	.
766917701	0	100000278	.
3246322201	0	100000319	.
2685599701	0	1000000001	.
528144401	0	1000000002	.
700417501	0	1000000003	.
25085425601	0	1000000004	.
27815597501	0	1000000005	.
27573710401	0	1000000006	.
31922994701	0	1000000007	.
1911361201	0	1000000008	.
1017008302	0	1000000009	.
2113442901	0	1000000010	.
28764486701	0	1000000011	.
612713702	0	1000000012	.
3041205401	0	1000000013	.
2880083601	0	1000000014	.
26057669701	0	1000000015	.
2034114301	0	1000000016	.
875018901	0	1000000017	.
739696902	0	1000000018	.
1721756701	0	1000000019	.
25071171301	0	1000000020	.
1483864202	0	1000000021	.
1403442602	0	1000000022	.
27349092302	0	1000000023	.
1106916102	0	1000000024	.
3362917301	0	1000000025	.
1534819502	0	1000000026	.
1776188402	0	1000000027	.
134297101	0	1000000028	.
3069867502	0	1000000029	.
1285039604	0	1000000030	.
616722802	0	1000000031	.
25181546702	0	1000000032	.
904269202	0	1000000033	.
1948658802	0	1000000034	.
26812174102	0	1000000035	.
1279537701	0	1000000036	.
962385202	0	1000000037	.
28227553201	0	1000000038	.
27042453101	0	1000000039	.
28152965301	0	1000000040	.
1777690801	0	1000000041	.
27098406401	0	1000000042	.
1966147201	0	1000000043	.
28636650602	0	1000000044	.
2230959601	0	1000000045	.
1724593601	0	1000000046	.
1910258902	0	1000000047	.
1175527802	0	1000000048	.
26446502002	0	1000000049	.
25391975401	0	1000000050	.
387316902	0	1000000051	.
1584455901	0	1000000052	.
26433872101	0	1000000053	.
1581658601	0	1000000054	.
590586801	0	1000000055	.
28894003001	0	1000000056	.
615986001	0	1000000057	.
30155211402	0	1000000058	.
1262267401	0	1000000059	.
1941428601	0	1000000060	.
1310180902	0	1000000061	.
970346101	0	1000000062	.
1567193302	0	1000000063	.
27419784001	0	1000000064	.
27565181001	0	1000000066	.
30099783501	0	1000000067	.
1733844401	0	1000000068	.
634358002	0	1000000069	.
602603202	0	1000000070	.
28676036801	0	1000000071	.
1540440002	0	1000000074	.
26805933201	0	1000000075	.
28348801601	0	1000000076	.
1843385102	0	1000000077	.
1531114501	0	1000000078	.
26959188001	0	1000000079	.
1720178502	0	1000000080	.
26764416601	0	1000000081	.
1536509302	0	1000000082	.
28496379901	0	1000000083	.
25184286901	0	1000000084	.
28630701001	0	1000000085	.
29468730101	0	1000000086	.
3203601201	0	1000000087	.
31111171901	0	1000000088	.
132381001	0	1000000089	.
865927601	0	1000000090	.
28388148102	0	1000000091	.
161108402	0	1000000092	.
1068476701	0	1000000093	.
596666402	0	1000000094	.
176151002	0	1000000095	.
28784354401	0	1000000096	.
2844805501	0	1000000099	.
899184702	0	1000000100	.
1229964802	0	1000000101	.
28353825302	0	1000000102	.
1544896101	0	1000000103	.
28581122501	0	1000000104	.
102320001	0	1000000105	.
1268511002	0	1000000107	.
28228933501	0	1000000108	.
28282698002	0	1000000109	.
1261811701	0	1000000111	.
1288923902	0	1000000112	.
1261088702	0	1000000113	.
26733352801	0	1000000114	.
28181476002	0	1000000121	.
663791101	0	1000000122	.
27569153901	0	1000000124	.
628119101	0	1000000129	.
879445602	0	1000000130	.
1481872302	0	1000000131	.
1258458402	0	1000000139	.
28172127001	0	1000000145	.
1554421902	0	1000000154	.
1268718302	0	1000000177	.
25052885201	0	10000000001	.
26689970302	0	10000000002	.
28638654001	0	10000000003	.
1467091501	0	10000000004	.
28375742502	0	10000000005	.
27453143101	0	10000000006	.
30734301	0	10000000007	.
619909802	0	10000000008	.
28097159302	0	10000000009	.
10624302	0	10000000010	.
26445831901	0	10000000011	.
747533601	0	10000000012	.
1294407601	0	10000000013	.
609432502	0	10000000014	.
27563609801	0	10000000015	.
1954845201	0	10000000016	.
25389597802	0	10000000017	.
961533701	0	10000000022	.
132711301	0	10000000024	.
26958953602	0	10000000027	.
26397540301	0	10000000030	.
1213517502	0	10000000031	.
1039734302	0	10000000041	.
1422049102	0	10000000042	.
2232880001	0	10000000049	.
;

dm log 'clear';


data want ;
 dcl hash H () ;
 h.definekey  ("mkey") ;
 h.definedata ("match_var") ;
 h.definedone () ;
 call missing(mkey);
 do _n_=1 by 1 until(z);
  set have end=z;
  array m match_1 match_2;
  if  match_1>. and match_2>. or case then do;
   match_var+1;
   do over m;
    rc=h.add(key:m,data:match_var);
   end;
  end;
 end; 
 do _n_=1 to _n_;
  set have ;
  do over m;
   if m>. then h.find(key:m);
  end;
  output;
 end;
 stop;
 drop mkey rc;
run;


proc sort data=want out=want_sorted;
by match_var;
run;

Now, I ask you, do you believe there will be a triplet for all cases as I seem to notice with some aren't with varying matches. Please review and let me know should some changes be required. 

You can test using

/*check where count of match_var ne 3 and verify why*/

proc sql;
create table check as
select *
from want_sorted
group by match_var
having count(*) ne 3;
quit;

Best Regards!

[Lefty]

Thank you! We are getting so close. You're right, for some of the other sets, the code mistakenly created some sets of 4 and some sets of 2. And I can see that even those that have 3 in a set are not always right. Funny the first about 400 rows look correct, then it seems to break down. Here is an example of a triplet that's incorrectly grouped (the first two rows should be grouped, but the third row should have match_1=1000000030):

ID

case

match_1

match_2

match_var

29503822602	0		10000000001	274
1149219701	1	1000000030	10000000001	274
25052885201	0	10000000001		274

I'm posting the data from the "check" dataset that has sets of 4 and 2. For the sets of 4, I can see instances when the set includes the same values for match_2 and match_1 (e.g., ID 602068803 should be in a set with match_1=10000068 [ID=2222362302] and where match_2=10000072. But it also includes the control (ID=25076003801) where match_1=10000072. When I looked my original data that i posted in my previous message (havesmall), I can see that the case ID= 26427415201 is supposed to match to the ID=25076003801 because both of their match_1 values are 10000072. Can your code be tweaked a bit to achieve this?

Thank you!! You have been so helpful!!

[novinosrin]

Hi @Lefty  I see what you mean. I looked into the data with some diligence and it seems this is akin to networking problem. It's little too late here and I've got to go home. But I will give it a shot tomorrow. Oh, somebody may have still answered  tonight while I was asleep.

Can you also post the expected sample for the input you posted plz?

[Lefty]

Of course, tomorrow is great!

Is the attached data what you were asking me to post? It's the all the matches of 3, some of which are correct and some not. It is the result of running this code:

proc sql;
create table Matches_of_3 as
select *
from want_sorted
group by match_var
having count(*) = 3;
quit;

Thank you again!

[novinosrin]

What I meant is a request to post a sample of your expected output aka results for the input. This would help me test my results against the expected results to see where the logic is missing and debug.  

[Lefty]

Sure, here are some rows of data in which the triplets are correctly assigned the same match_var. Let me know if that's not what you had in mind.

Thank you so much again!