I agree with Ksharp and DLing that the problem will likely require an iterative macro. However, given your sample data, the duplicate issue was resolved with one iteration, thus there isn't enough data there to be able to test whether any macro we might build is actually sufficient.
The alternative, of course, is that you simply run the iterations and tests manually. In the following code, I run the proposed hash solution, check for discrepacies, correct the discrepancies in a subsequent run of the proposed hash solution, and then test the result.
If you do end up incorporating the logic in a macro, it would be appreciated if you post your final code.
The code and data I used was:
data have;
infile cards dsd dlm='|' truncover;
input (pan1 add1 pan2 pan3) (: $40.);
cards;
M5669K|4HH5BFWBBMBY48Y7P8S|P5P8YBBMBY48Y7P8S0005D|
I2175Q|P5P8YBBMBY48Y7P8S0005D||
S1495P|8BWP_Y8V3MYVWBBMBBMB||
G4909P|WN8Y4YFN48FYP884P83Y4||
|S1495P||
S0560L|488Y8N8N8H-K56H4YP8P||
F0994B|R8YPF8Y4P-0BWP8BBMBY||
H6408L|HICPM4BBPWFP4WV4MP8YBB||
M2091M|48YJ23N3P8VWBBMBY48Y7||
S2426K|5V4PZHKB8YPNPVWPVBBMBY||
D7907L|SBMBMVYYBBM8PBY48Y7P8S0|H6408L|
G9847L|XB3845-WM43YWBBMBY48Y7||
D3871H|D3PC34Z0-38W3YBBMBY48Y||
T0753D|2P8Y38Y84W3N4NPJ2R8P8Y||
P5328F|Z06V4B8YFYFYMY8FMYVWBB||
D3938K|KSJ2VBYWBY4P8YBBMBY48Y||
A3529D|K0K4F4BY8BBXVBBMBY48Y8||
P8617A|I0S3YPV4P8BYVWMYVWBBM4||H6408L
R1129A|Z0KMV4YWM483NBBMB8Y7P8S||
R|KK0ZJ2PYJ2NW8K5SH8BV84||
S7807B|YPFBYBP4WNWN88YBMBBMBY||
N8681Q|KHH4B8MMYNW3Y8YBBMBY48||
P8632Q|H550HFMBM4YPFYFPBBMBY4||
K3437H|DZ6MYVNN3YWMBY48Y7P8S0||
G5510C|ZH@YPBY84BB$$$$$$$$$||
R2518M|HZB8NV8MWXYBBY48Y7P8S00||
M1014A|B8XY5IZH_38VWBBMBBMBY4||
M0049P|KZYJ2C8Y3BNCVY4VP8YBBM||
PD4296R|K0KVPJ23YV43PFY3P8VWBB||
L5351M|KF47PP8NPCN4VWNWBBMS00||
D7744C|56PW3WF3W8P3P8VWBBMBY4||
U1088L|#8B8WP8FPMP3YWBBMS0005||
U1088L|#8B8WP8FPMP3YWBBMBY48Y||
U0564D|#8B8WP8FPMP3YWBBMBY48Y||
B0102C|@P7P8W4@PZIYBWDSZ4M_W||
U1088L|#8B8WP8FPMPWBBMBY48Y||
U0564D|#8B8WP8FPMPYWBBMBY48Y||
S9556K|@P7P8W46CN8SZ_WGP48PBB||
S9556K|@P7P8W46CN8SZ_WGP48PBB||
B1476E|S05YCGY4N4CYHZKPYBPBBMS||
C2940N|56PW3WF3W8P3P8VWBBMBY4||
U0564D|#8B8WP8FPMP3YWBBMBY48Y||
R0090B|_XN4S2YJ2F8MBY48Y7P8S0||
N8784Q|S0K4PY4WD65YWBBMBY48Y7||
G8553G|KZBP8SFYFPNYBBMBY48Y7P||
S5904H|60KZZ2Y3NCP5J2Y3N3P8VW||
S3482C|Z05Y4YJ24VVYWN884YBBMB||
U1088L|#8B8WP8FPMPYWBBMBY48Y||
K3031C|ZH0HF8YPFY84Y83NBBMBY||
R0090B|_XN4S2YJ2F8MBY48Y7P8S0||
P4466N|IHY48CH84YBBY48Y7P8S00||
J2314G|5WXBM8Y3YWBBBMBY48Y7P8||
R0090B|_XN4SJ2YJ2F8MBY48Y7P8S0||
TI7823D|78MH-ZZ3M8GYBBMBBMBY48||
B0509R|50Z88B5V48YN4FYFPBBMB||
M0682N|B4YGWP443YPY3WMBBMBY48||
K0725D|ZH0HF8YPFY8Y4Y83NBBMBY||
P9185M|Z-8WX6SW8BPBMBY48Y7P8S||
V2636H|Z05WWMFJ2YC4P4WWMFBBMBY||
K9954E|MJ2FPFYBCBBY48Y7P8$$$$||
PS0210M|S0-SBBW88YW8BBMBY48Y7P||
PS5842M|38FW-WXBV4FWX_BNWXBBMB||
M5480A|50Z44YWVWNWMBY48Y7P8S0||
V2825L|SCF84C@WBWBYBYWP8YBBMB||
J9090J|BM83Y8P4NN6V883NBBMBY4||
G9332F|Z0SZ4W8HV8FPBBMBY48Y7P||
M2017K|K6KDNPJ28ZK4YBBMBY48Y7||
M6901D|I-NPX488V3YPBBMBY48Y7P8||
S0689N|Z0ZZP2YF367YVWBBMBBMBY|M5669K|
M4134E|DNB4FHZIHZ43Y4BBMBY48Y||
G7604R|5-Y8PRPMWPF4YBBMBY48Y7||
S3468A|HCFP48FWWV88BBMBY48Y7P||
S0796N|K0SP3KPVFYWBWP8BBMBY48||
D7781Q|H0DN48484P8883NBBMBY48|S2395K|
B4607P|HBPFWIS3N3YBMBY48Y7P8S||
C4398Q|K0HKCYBX8YMKMYVWBBMBY4||
J9787E|SNMZD4PB8PFBBMBY48Y7P8|D7781Q|
D1187E|BYP8BWBY4FPP8YBBMBBMBY||S2395K
K2110J|HKS6FYNWFYHMWBBMBY48Y7P||
M5116F|S5SIB8BP85GBBMBY48Y7P8||
M3134J|6HWJ2P78NVBBY48Y7P8S00||D7781Q
P1590P|S0Z4PYVY84PBBMBY48Y7P8|M3134J|
S9598G|KZWXB48VYBPBBMBY48Y7P8||
K9168B|-HJ2M88MJ2WMBY48Y7P8S0|H3629M|
S2395K|ZSZ48PF#NFVBW8BBMBBMBY||
M2140L|48VP33W8BNB8BW8BBMBBMB||H3629M
B4952H|YJ2PB3CN8MYBBMBY48Y7P8|S9598G|S2395K
S5018D|KZ0HVMWM4Y3BBMBY48Y7P8||
K5943E|Z6PP8VPM83N8BPMV8BBMBY||
N6677M|ZKYCSVW8BF8VWBBMBY48Y7|M5669K|
HJ7138K|MV4YK08W38BBMBY48Y7P8S||
| | |
R9004J|K0KCNP7P85SBBMBY48Y7P||
F0109Q|Z8W8SSVY4VYNM8VBBMBBMB|R9004J|M2724Q
M2724Q|ZZZ54RFP8GYBMBBMBY48Y7||
D9109E|YVYB4W46SWYBMBBMBY48Y7P||
M9585E|KBGWY3YB2WBMBY48Y7P8S00||
| | |
H3629M|-_P8VY-0WP8BMBY48Y7P8S0||
T2426B|50ZYC8NHI8YBCVCFYP8BBM|M9295K|
M9295K|Z5ZMPFV8M8BMBBMBY48Y7||
P3089F|K6KDNPJ28ZK4YBBMBY48Y7||
PO2499R|-K067P8KPYBMBY48Y7P8S0||
L3447Q|33PV4V5I633F4YBBMBY48Y|M5669K|
1088L|||
;
run;
/*Assign initial household numbers*/
data for_format (keep=start stop label);
if _n_ eq 1 then do;
declare hash ha(hashexp: 16);
ha.definekey('key');
ha.definedata('hhold');
ha.definedone();
end;
set have;
array _house{*} $ 40 pan1--pan3;
do i=1 to dim(_house);
key=_house{i};
call missing(hhold);
rc=ha.find();
if rc=0 then do;
found=1;
label=hhold;
leave;
end;
end;
if not found then do;
n+1;
label=n;
end;
do j=1 to dim(_house);
if not missing(_house{j}) then do;
key=_house{j};
hhold=label;
ha.replace();
start=key;
stop=key;
output;
end;
end;
run;
/*Test for invalid assignments*/
proc sort data=for_format out=test nodupkey;
by start label;
run;
data test2;
set test;
by start;
if not(first.start and last.start);
run;
/*Correct identified discrepancies*/
data want (keep=pan1 pan2 pan3 add1 household);
if _n_ eq 1 then do;
declare hash ha(hashexp: 16);
ha.definekey('key');
ha.definedata('hhold');
ha.definedone();
end;
set have;
array _house{*} $ 40 pan1--pan3;
do i=1 to dim(_house);
key=_house{i};
call missing(hhold);
rc=ha.find();
if rc=0 then do;
found=1;
IF HHOLD EQ 64 THEN HHOLD=59;
household=hhold;
leave;
end;
end;
if not found then do;
n+1;
household=n;
end;
do j=1 to dim(_house);
if not missing(_house{j}) then do;
key=_house{j};
IF HOUSEHOLD EQ 64 THEN HOUSEHOLD=59;
hhold=household;
ha.replace();
end;
end;
run;
/*Test for invalid assignments*/
data test (keep=start household);
set want;
array _house{*} $ 40 pan1--pan3;
do i=1 to dim(_house);
if _house(i) ne "" then do;
start=_house(i);
output;
end;
end;
run;
proc sort data=test nodupkey;
by start household;
run;
data test2;
set test;
by start;
if not(first.start and last.start);
run;
