topic Re: Hash join question in SAS Procedures

Hash join question

evp000 — Mon, 09 Mar 2015 17:43:22 GMT

Hi,

In Art Carpenter's Innovative SAS Techniques, Chapter 3, section 3.3.7 there is the following code:

data trans;

length lname $10 fname $6 sex $1;

fname = 'Mary'; lname = 'Adams'; sex = 'N'; output;

fname = 'Joan'; lname = 'Adamson'; sex = 'x'; output;

fname = 'Peter'; lname = 'Anla'; sex = 'A'; output;

run;

data newdemog (drop = rc);

declare hash upd(hashexp:10);

upd.definekey('lname','fname');

upd.definedata('sex');

upd.definedone();

do until(lasttrans);

set trans end=lasttrans;

rc=upd.add();

end;

do until(lastdemog);

set advrtp.demog end=lastdemog;

rc=upd.find();

output newdemog;

end;

stop;

run;

Was it necessary to have the first loop to populate the hash table, or would it have been the same to just declare it with

declare hash upd (dataset: 'trans'); ?

Does this code do the same thing (without do loops)?

data newdemog (drop = rc);

if 0 then set advrtp.demog trans ;

if _n_ = 1 then

do;

declare hash upd(hashexp:10 dataset: 'trans' );

upd.definekey('lname','fname');

upd.definedata('sex');

upd.definedone();

end;

set advrtp.demog;

call missing(sex);

rc = upd find();

output;

run;

Thanks for your input.

Re: Hash join question

Haikuo — Mon, 09 Mar 2015 21:17:20 GMT

When asking if they are the same, you are implying several things at the same time, for example:

1. Will they get the same outcome? Yes, I believe so.

2.Will they get the same/similar performance/efficiency? I have no clue. You will have to benchmark it yourself .

in between REPLACE() and ADD() methods, REPLACE() is the one that will produce different outcome if you have duplicated keys.

Haikuo

Re: Hash join question

gergely_batho — Mon, 09 Mar 2015 21:23:18 GMT

Yes, the two codes produce the same output (though I have not tested).

I think in older versions of SAS there was no dasaset argument tag.

Re: Hash join question

evp000 — Tue, 10 Mar 2015 03:07:44 GMT

Ok, thanks. Yes, I was wondering about the output, not the performance.

Re: Hash join question

evp000 — Tue, 10 Mar 2015 03:08:55 GMT

Thanks.