Thanks Tom. Looking closer, it was indeed an inner join. Seeing the original data, however, I still found something that doesn't add up, because the presence of additional fields creates double lines in the sql that disappear in the hash. Here is an example, I think it can be solved by introducing multidata="Y" also on the second hash:
data OPE;
input cod_contratto :$15. NUM_REVISIONE TS_FINE_VALIDITA :datetime.;
format TS_FINE_VALIDITA datetime19.;
datalines;
100004790 35 01Jan1999:00:00:00
;
data AA;
input cod_contratto :$15. NUM_REVISIONE extra ;
datalines;
100004790 35 1
100004790 35 2
;
proc sql;
create table W1140ABS as
select distinct
aa.cod_contratto,
AA.NUM_REVISIONE,
AA.extra,
OPE.TS_FINE_VALIDITA
from AA
inner join OPE
ON AA.COD_CONTRATTO = OPE.COD_CONTRATTO
and OPE.NUM_REVISIONE = AA.NUM_REVISIONE;
quit;
data W1140ABS_hash;
if 0 then set OPE AA;
if _n_=1 then do;
declare hash h_merge(dataset:"OPE",multidata:'Y');
rc = h_merge.DefineKey("COD_CONTRATTO", "NUM_REVISIONE");
rc = h_merge.DefineData( "TS_FINE_VALIDITA");
rc = h_merge.DefineDone();
declare hash h_left();
rc = h_left.DefineKey("COD_CONTRATTO", "NUM_REVISIONE");
rc = h_left.DefineData("RC");
rc = h_left.DefineDone();
end;
set AA end=eof;
if 0=h_left.add() then do;
rc = h_merge.find();
do while(not rc);
output;
rc = h_merge.find_next();
end;
end;
drop rc ;
run;
... View more
