topic Re: HASH full and then a left join with the same table in SAS Programming

HASH full and then a left join with the same table

bebess — Tue, 17 Nov 2020 12:30:43 GMT

DATA TABLEA;
input Name $1. ID $5. smoker_code Year;
Cards;
A ID1 0 2012
B ID2 1 2010
;
Run;

DATA TABLEB;
input Name $1. ID $5. want Year;
Cards;
A ID1 0.5 2010
A ID1 0.6 2011
A ID1 0.7 2012
B ID2 0.8 2008
B ID2 0.8 2009
B ID2 0.8 2010
B ID2 0.9 2011
;
Run;

Proc sql;
Create table ABC_SQL_full as
select A.*
,B.want as want1
,B.year as Year_past
,C.want as Want2

from (select *,MONOTONIC() as ORD from TABLEA) as a

full join TABLEB as b
on A.ID=B.ID
and A.Name=B.name

left join TABLEB as c
on A.ID=c.ID
and a.Name=c.name
and b.year=c.year

where b.year le a.year
order by ORD,year_past;
quit;

/* how to perform the same SQL join results with HASH method

1.HASH full join with tableB
2.and HASH left join with same tableB with condition b.year=c.year in the defined key
3.and having the same where and order condition*/

/* here it's an left join in hash that i can use for the second join ... */
data ABC_HASH_full;
if 0 then set TABLEB ;

set TABLEA ;

if _n_=1 then
do;
declare hash hhh(dataset: "TABLEB", multidata:'y');
hhh.DefineKey('ID','Name');
hhh.DefineData('want');
hhh.DefineDone();
end;

if hhh.find(key:ID,Key:Name)=0 then do;
output ABC_SQL_full;
end;
run;

Re: HASH full and then a left join with the same table

PeterClemmensen — Tue, 17 Nov 2020 12:49:51 GMT

See if you can use this as a template

data hash_full;
   if _N_ = 1 then do;
      dcl hash h(dataset : "TABLEB(rename=Year=Year_Past", multidata : "Y");
      h.definekey("Name", "ID");
      h.definedata("want", "Year_Past");
      h.definedone();
   end;

   set TABLEA;
   Year_Past = .;want = .;

   do while (h.do_over() = 0);
      if Year_Past <= Year then output;
   end;
run;

Re: HASH full and then a left join with the same table

PeterClemmensen — Wed, 18 Nov 2020 09:19:02 GMT

@bebess is this the answer you're after?

Re: HASH full and then a left join with the same table

bebess — Wed, 18 Nov 2020 09:20:41 GMT

Thank you it seems to work as i want. the only question i have is how to sort the output table like in my SQL example by ORD ( keep order of TABLEA table ) ?
Maybe it's not possible in that case to do it during the HASH, maybe i have to add another data step / sql step ....

Re: HASH full and then a left join with the same table

bebess — Wed, 18 Nov 2020 09:24:04 GMT

Yes definitely great @PeterClemmensen

Re: HASH full and then a left join with the same table

PeterClemmensen — Wed, 18 Nov 2020 09:26:43 GMT

The Set Statement read TableA sequentially, so the order from TableA will be intact in the output data set 🙂