Tourmaline | Level 20

Re: Creating variable that links rows from 1:2 match

If you could post some expected correct ones for the incorrect ones, that would help.

What I means to ask or say is,  should you have something like the below

1   2

5  8

3 9

2  5

Let's begin matching

Take 1, look up , no matches found. Since this is starting, increment case counter =1;

Take 2, perform look up, look forward 3 records  where you find a match with 4th records 2 5, so case counter for 1 2 and 2 5 is 1. However 5 matches with 5 8 in 2nd record, so again the case counter is still 1 for 1 2, 2 5, 5 8 .

Next starting from 3 9, is a new start, so increment case counter by 1, so case counter=case counter +1 which is equal to 2. Repeat the process again until all records are scanned.

Is my understand correct?

Obsidian | Level 7

Re: Creating variable that links rows from 1:2 match

Ah, I see. The reason your example does not quite do what I'm looking for is because the second 5 in the 2 5 row should NOT match to the first 5 in the 5 8 row. I want only equal values of match_1 to match to each other, and only equal values of match_2 to match to each other. So for example in this wrong triplet, the match_2 in the first two rows is correct, but match_1 in the second two rows is not equal and therefore incorrect:

 ID case match_1 match_2 26229681701 0 100000025 1549255201 1 100000021 100000025 28654518402 0 100000025

I would instead like it to look like this:

 ID case match_1 match_2 26229681701 0 100000025 1549255201 1 100000021 100000025 2280537501 0 100000021

Does that help? Thank you!!!

@novinosrin wrote:

If you could post some expected correct ones for the incorrect ones, that would help.

What I means to ask or say is,  should you have something like the below

1   2

5  8

3 9

2  5

Let's begin matching

Take 1, look up , no matches found. Since this is starting, increment case counter =1;

Take 2, perform look up, look forward 3 records  where you find a match with 4th records 2 5, so case counter for 1 2 and 2 5 is 1. However 5 matches with 5 8 in 2nd record, so again the case counter is still 1 for 1 2, 2 5, 5 8 .

Next starting from 3 9, is a new start, so increment case counter by 1, so case counter=case counter +1 which is equal to 2. Repeat the process again until all records are scanned.

Is my understand correct?

Tourmaline | Level 20

Re: Creating variable that links rows from 1:2 match

Hi @Lefty  First off, Thank you for your patience and most importantly for being responsive. I think I've understood the requirement pretty well this time. Please try the below and let me know. Thank you!

``````
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  ("_i_","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 case then do;
match_var+1;
do over m;
end;
end;
end;
do _n_=1 to _n_;
set have ;
do over m;
if m>. then h.find(key:_i_,key:m);
end;
output;
end;
stop;
drop mkey rc;
run;

/*Final*/
proc sort data=want out=want_sorted;
by match_var;
run;

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

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

Again, Many thanks for your patience ans sincerity. Cheers!

Obsidian | Level 7

Re: Creating variable that links rows from 1:2 match

Amazing!! It works perfectly. I am so appreciative of your help, I never would have figured that out on my own! Thank you!

Tourmaline | Level 20

Re: Creating variable that links rows from 1:2 match

The pleasure is all mine. Also, feel free to let us know if we can be of help.

Discussion stats
• 19 replies
• 2158 views
• 2 likes
• 3 in conversation