FWIW, I posted the following on SAS-L ( http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1109B&L=sas-l&D=1&H=0&O=D&T=1&P=10727 ) at the same time this discussion was going on in the forums last September. Most of the ideas are KSharps, although a number of others offered suggestions I I really can't recall who should be credited with what. Hopefully, you can use one of the methods to address your current problem:
data have;
infile cards truncover;
input (pan1 add1 pan2 pan3) (: $40.);
cards;
aaa bbb ccc ddd
qqq rrr www aaa
rrr ppp mmm lll
uuu zzz ffff ppp
p l m n
m aaa
a b c .
jjjj eee rrr ooo
. . . .
e f . .
a e . .
g h . .
i j k .
i j k a
sss www . .
. . . eee
;
run;
My macro/hash solution was:
%macro findthem;
proc datasets library=work nowarn;
delete recodes;
quit;
%let stopthis=0;
/*Assign initial household numbers*/
data for_recodes (keep=start hhold);
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;
leave;
end;
end;
if not found then do;
n+1;
hhold=n;
end;
do j=1 to dim(_house);
if not missing(_house{j}) then do;
key=_house{j};
ha.replace();
start=key;
output;
end;
end;
run;
%let recodes=;
%do %while (&stopthis eq 0);
/*Test for invalid assignments*/
proc sort data=for_recodes out=test nodupkey;
by start hhold;
run;
data test2 (drop=n);
set test end=eof;
by start;
if not(first.start and last.start) then do;
output;
n+1;
end;
if eof and n lt 1 then do;
call symput('stopthis',1);
end;
run;
%if &stopthis eq 0 %then %do;
proc append base=recodes data=test2 force;
run;
proc sql noprint;
select "if hhold eq "||
strip(put(hhold,best12.))||
" then hhold="||
strip(put(min(hhold),best12.))||
";"
into :recodes
separated by " "
from recodes
group by start
;
quit;
/*Correct identified discrepancies*/
data for_recodes (keep=start hhold);
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;
&recodes.
leave;
end;
end;
if not found then do;
n+1;
hhold=n;
&recodes.
end;
do j=1 to dim(_house);
if not missing(_house{j}) then do;
key=_house{j};
ha.replace();
start=key;
output;
end;
end;
run;
%end;
%end;
/*Get Final Dataset*/
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;
&recodes.
household=hhold;
leave;
end;
end;
if not found then do;
n+1;
hhold=n;
&recodes.
household=hhold;
end;
do j=1 to dim(_house);
if not missing(_house{j}) then do;
key=_house{j};
ha.replace();
end;
end;
run;
%mend findthem;
%findthem
Ksharp's pure hash solution was:
data want(keep=pan1 pan2 pan3 add1 household);
if _n_ =1 then do;
declare hash ha(hashexp : 20,ordered: 'Y');
declare hiter hi('ha');
ha.definekey('count');
ha.definedata('count','_pan1','_pan2','_pan3','_add1','searched');
ha.definedone();
do until(last);
set have(rename=(pan1=_pan1 pan2=_pan2 pan3=_pan3 add1=_add1))
end=last;
count+1;
searched=0;
if not missing(_pan1) or not missing(_pan2) or
not missing(_pan3) or not missing(_add1) then ha.add();
end;
declare hash _ha(hashexp: 20);
_ ha.definekey('key');
_ha.definedata('_household');
_ha.definedone();
end;
set have ;
_count+1;
length key $ 40;
array h{4} $ 40 pan1 pan2 pan3 add1;
array _h{4} $ 40 _pan1 _pan2 _pan3 _add1;
count=_count;searched=0;
r=ha.find();
if not searched then do;
n+1;
household=n;
_household=n;
do i=1 to 4;
if not missing(h{i}) then do;
key=h{i};
_ha.replace();
end;
end;
do until(x=1);
x=1;
rc=hi.first();
do while(rc=0);
if not searched then do;
_found=0;
do j=1 to 4;
key=_h(j);
_rc=_ha.check();
if _rc =0 then _found=1;
end;
if _found then do;
do k=1 to 4;
if not missing(_h{k}) then do;
key=_h(k);
_ha.replace();
end;
end;
searched=1;
ha.replace();
x=0;
end;
end;
rc=hi.next();
end;
end;
end;
else do;
do z=1 to 4;
key=h{z};
rr=_ha.find();
if rr=0 then household=_household;
end;
end;
run;
