DATA Step, Macro, Functions and more

HASH Object - Multiple SQL LEFT JOIN

Reply
Frequent Contributor
Posts: 83

HASH Object - Multiple SQL LEFT JOIN

Hi

I need help to create a hash join equivalent to using two left joins , on total 3 tables like

Proc sql;

create table test as select * from

Table1 as A  left join

Table2 as B on (A.id=B.id)    left join

Table3 as  on (A.key=C.Key)

; quit;

For one table the following would work

assuming Table  T1 >  Table T2

data SET1;

     if 0 then set Table2 ;

declare hash VS(hashexp:7, dataset:Table2 );

VS.definekey('KEY'');

VS.definedata(all:'Y');

VS.definedone();

do until(eof);

set Table1 end=eof;  

   if VS.find()=0 then output;

else do;

call missing(of T1-T2);

output;

end;

end;

stop; run;

SAS Employee
Posts: 27

HASH Object - Multiple SQL LEFT JOIN

You can have multiple hash objects in one data step.

PROC Star
Posts: 1,558

Re: HASH Object - Multiple SQL LEFT JOIN

Unsure what the issue is.

This should do what you want and be faster than SQL, provided the tables fit in memory and have unique keys of course.

 

data SET1;
  set TABLE1 ;  
  if _N_=1 then do;
    declare hash VS( dataset:'TABLE2' );
    VS.definekey('KEY');
    VS.definedata(all:'Y');
    VS.definedone();

    declare hash VS1( dataset:'TABLE3' );
    VS1.definekey('KEY');
    VS1.definedata(all:'Y');
    VS1.definedone();
    if 0 then set TABLE2 TABLE3;
  end;
  RC=VS.find();
  RC=VS1.find();
  drop RC;
proc print;run;


To ensure the hash table variables are set to missing before retrieval, use the --  variable list shortcut.

data TABLE1;
  array B {5};
  KEY=1; B1=2;  output;
  KEY=2; B1=.;  output;

data TABLE2;
  array C {5};
  KEY=1; C1=2;  output;
  KEY=2; C1=.;  output;

data TABLE3;
  array D {5};
  KEY=1; D1=2;  output;
  KEY=2; D1=4;  output;


data SET1;
  set TABLE1 ;  
  if _N_=1 then do;
    declare hash VS( dataset:'TABLE2' );
    VS.definekey('KEY');
    VS.definedata(all:'Y');
    VS.definedone();

    declare hash VS1( dataset:'TABLE3' );
    VS1.definekey('KEY');
    VS1.definedata(all:'Y');
    VS1.definedone();
    if 0 then set TABLE2 TABLE3;
  end;
  call missing(of C1--D5);
  RC=VS.find();
  RC=VS1.find();
  drop RC;
proc print;run;

Super User
Posts: 9,662

Re: HASH Object - Multiple SQL LEFT JOIN

I am very interesting about your questio.

But since you did not post some sample data,so it is hard to give a exact code. This is an example.

Whether it is what you want.

Maybe in the future, I will use Hash Table to make left join and Product Cartesian . Thank you . Smiley Happy

data class1(keep=name sex age) ;
 set sashelp.class;
run;
data class2(keep=sex weight rename=(sex=_sex));
 set sashelp.class;
 if ranuni(-1) le .8;
run;
data class3(keep=name height rename=(name=_name));
 set sashelp.class;
 if ranuni(-1) ge .8;
run;
data _want(drop=a1 _: rc found);
 set class1;
if _n_ eq 1 then do;
 declare hash ha1(hashexp:10);
 declare hiter _ha1('ha1');
  ha1.definekey('_sex','a1');
  ha1.definedata('weight','_sex');
  ha1.definedone();

do until(last);
 set class2 end=last;
 a1+1;
 ha1.add();
end;

end;



rc=_ha1.first();
do while(not rc);
rc=_ha1.next();
if sex=_sex then do;output; found=1;call missing(a1,_sex,weight);end;
end;
if not found then do;call missing(a1,_sex,weight);output;end;

run; 
data want(drop= a2 _: rc found);
 set _want;
if _n_ eq 1 then do;

 declare hash ha2(hashexp:10);
 declare hiter _ha2('ha2');
  ha2.definekey('_name','a2');
  ha2.definedata('height','_name');
  ha2.definedone();


do until(_last);
 set class3 end=_last;
 a2+1;
 ha2.add();
end;

end;


rc=_ha2.first();
do while(not rc);
rc=_ha2.next();
if name=_name then do;output;found=1;call missing(a2,_name,height);end;
end;
if not found then do;call missing(a2,_name,height);output;end;

run; 

Ksharp

Ask a Question
Discussion stats
  • 3 replies
  • 3414 views
  • 1 like
  • 4 in conversation