topic Re: Hash Tables Joining with Large number of Variables in SAS Procedures

Hash Tables Joining with Large number of Variables

DrBigAl — Mon, 06 Aug 2018 17:18:06 GMT

I am running into a problem with big data inner joining. I created a hash object but not sure how to use all variables without typing all 200 variable names into the definedata() and call missing(). Should I be using the call missing()? please take a look at my code and give any suggestions....

data ltdmerg (drop=rc);
if 0 then set bigdata.file;
declare hash hh_pat(dataset:"bigdata.file");
rc=hh_pat.defineKey("id_cd");
rc=hh_pat.defineData(ALL:'Yes');
rc=hh_pat.defineDone();
do until(eof);
set work.data end=eof;
call missing (?????);
rc=hh_pat.find();
if rc=0 then output;
end;
stop;
run;

Re: Hash Tables Joining with Large number of Variables

novinosrin — Mon, 06 Aug 2018 17:25:20 GMT

When loading a dataset into a hash table, you don't need call missing for the reason

if 0 then set bigdata.file;

will set the PDV host variable equivalent of hash variable names .So if i could grasp what I have read and comprehended so far on reading the almighty's incarnation PD's book, I think I am right.

Re: Hash Tables Joining with Large number of Variables

novinosrin — Mon, 06 Aug 2018 17:28:13 GMT

an illustration:

data w;
set sashelp.class;
keep name;
run;
data want (drop=rc);
if 0 then set sashelp.class;
declare hash hh_pat(dataset:"sashelp.class");
rc=hh_pat.defineKey("name");
rc=hh_pat.defineData(ALL:'Yes');
rc=hh_pat.defineDone();
do until(eof);
set w end=eof;
rc=hh_pat.find();
if rc=0 then output;
end;
stop;
run;

Re: Hash Tables Joining with Large number of Variables

novinosrin — Mon, 06 Aug 2018 17:37:15 GMT

Here's another version of setting the PDV host variable equivalent. I call it PD's style :). Love it when he toils with SAS coding

data want (drop=rc);
declare hash hh_pat(dataset:"sashelp.class");
rc=hh_pat.defineKey("name");
rc=hh_pat.defineData(ALL:'Yes');
rc=hh_pat.defineDone();
do until(eof);
set w end=eof;
rc=hh_pat.find();
if rc=0 then output;
end;
stop;
set sashelp.class;/*notice here*/
run;

I remember PD's style as PDV=~~Program data~~ vector corrected as Paul dorfman's Vector aka PDV host vars

Recommended reading the book title and notes are below:

https://communities.sas.com/t5/Community-Matters/Book-Data-Management-Solutions-Using-SAS-Hash-Table-Operations-A/td-p/433642

Re: Hash Tables Joining with Large number of Variables

DrBigAl — Mon, 06 Aug 2018 19:35:08 GMT

Thanks! Took missing out and works great!

Re: Hash Tables Joining with Large number of Variables

ChrisNZ — Tue, 07 Aug 2018 01:31:45 GMT

If you had a left join, this could be used to avoid listing all the variables:

data W;
  set SASHELP.CLASS(keep=NAME) ;
  output;
  if _N_=1 then do; NAME='x'; output; end;
run;
data want (drop=rc);
  if _N_=1 then do;
    if 0 then set SASHELP.CLASS;
    declare hash hh_pat(dataset:"SASHELP.CLASS");
    rc=hh_pat.defineKey("NAME");
    rc=hh_pat.defineData(ALL:'Yes');
    rc=hh_pat.defineDone();
  end;
  set W;
  call missing(of SEX -- WEIGHT);
  rc=hh_pat.find();
run;