topic Re: Hash table: same key value to subset and output multiple data tables in SAS Programming

Hash table: same key value to subset and output multiple data tables

windlove — Thu, 07 Nov 2019 03:58:21 GMT

I have a lookup table to be used for multiple datasets.

How can I use hash table method to perform subset and output for each of them in the same step?

I have experimented the following, but clearly it is not working.

data outA outB;
	
    if 0 then set lookup(keep=ppn);
    if _n_ = 1 then do;
        declare hash hash(dataset: 'lookup(keep=ppn)');
                hash.defineKey('ppn');
                hash.defineDone();
    end;

    do until(last);
        set tableA end = last;
        if ~hash.find() then output outA;
    end;

    do until(last);
	set tableB end = last;
	if ~hash.find() then output outB;
    end;
run;

Re: Hash table: same key value to subset and output multiple data tables

Patrick — Thu, 07 Nov 2019 04:19:51 GMT

The main thing is to reset variable LAST before the 2nd do-while loop.

I would use the check() method because you don't need to read the value from the hash table into the PDV. You just want to check for existence.

I've never seen the ~ used in front of the hash reference. It appears to work but can you explain to me what's that for?

Here your code amended - inclusive a few cosmetic changes.

data tableA tableB lookup;
  set sashelp.class;
  ppn=name;
  output tableA;
  output tableB;

  if _n_<4 then
    output lookup;
run;

data outA outB;
  if 0 then set lookup(keep=ppn);

  declare hash h1(dataset: 'lookup(keep=ppn)');
  h1.defineKey('ppn');
  h1.defineDone();

  do until(last);
    set tableA end = last;
    if h1.check()=0 then output outA;
  end;

  last=0;
  do until(last);
    set tableB end = last;
    if h1.check()=0 then output outB;
  end;

  stop;
run;

Re: Hash table: same key value to subset and output multiple data tables

windlove — Thu, 07 Nov 2019 04:34:36 GMT

Thanks Patrick.

"~hash.check" is the same as "hash.check = 0".

This is what I learnt when someone taught me hash table method for the first time. Then got used to it.

Your solution partially worked. But both data were merged.

Is there anyway I can prevent them from merging?

Re: Hash table: same key value to subset and output multiple data tables

Patrick — Thu, 07 Nov 2019 04:45:52 GMT

What do you mean by merging?

It's certainly not merging rows but it will add all the variables from the source tables to the PDV and though to the target tables. You can use a data set KEEP option to prevent this from happening.

data outA(keep=<variable list>) outB(keep=<variable list>);

Re: Hash table: same key value to subset and output multiple data tables

windlove — Thu, 07 Nov 2019 04:47:08 GMT

You are absolutely right. Thank you.

Re: Hash table: same key value to subset and output multiple data tables

FreelanceReinh — Thu, 07 Nov 2019 10:22:31 GMT

@Patrick wrote:

I've never seen the ~ used in front of the hash reference. It appears to work but can you explain to me what's that for?

It's just one of the (operating system dependent) symbols for the logical operator NOT, by no means specific to hash object syntax. (Strangely enough, the linked documentation seems to contain a typo: ° instead of ^ as it's correctly listed in WHERE syntax.)