Hash table finding key

Matt3 — Fri, 08 Dec 2017 09:00:15 GMT

Hi,

Could anyone please tell me If is it possible to check if record with specific keys and without other key exist in table by hash table (find() or check() method):

green color is what am trying to do:

   proc sql;
       create table sample3 as
       select t2.zm_cel, t3.zm_cel as zm_cel_sweek,t4.zm_cel as zm_cel_1week,t5.zm_cel as zm_cel_2week,t6.zm_cel as zm_cel_3week,t7.zm_cel as zm_cel_4week, t1.* from temp t1 left join temp2 t2
       on (t1.id_pesel=t2.id_pesel and t1.week=t2.week and t1.date_record_generation=t2.date_record_generation)
       left join temp2 t3
          on (t1.id_pesel=t2.id_pesel and t1.week=t3.week and t1.date_record_generation<>t3.date_record_generation)
       left join temp2 t4
          on (t1.id_pesel=t2.id_pesel and (t1.week_m1=t4.week or t1.week_p1=t4.week))
       left join temp2 t5
          on (t1.id_pesel=t2.id_pesel and (t1.week_m2=t5.week or t1.week_p2=t5.week))
       left join temp2 t6
          on (t1.id_pesel=t2.id_pesel and (t1.week_m3=t6.week or t1.week_p3=t6.week))
       left join temp2 t7
          on (t1.id_pesel=t2.id_pesel and (t1.week_m4=t7.week or t1.week_p4=t7.week));
       quit;

I would like to use hash table because temp2 table is relatively small and sql method is not efficient.

Re: Hash table finding key

mkeintz — Fri, 08 Dec 2017 13:13:34 GMT

Let's say you want all the records in dataset HAVE, which match variable A in dataset TEST, but do not match the combination of A&B in TEST. Although it could be done with a single hash object, this two-hash-object solution (untested) is simpler:

data want;
  set have;
  if _n_=1 then do;
    declare hash h1 (dataset:'test (keep=a)');
      h1.definekey(all:'Y');
      h1.definedone();
    declare hash h2 (dataset:'test (keep=a b)');
      h2.definekey(all:'Y');
      h2.definedone();
  end;

  rc1=h1.check();
  if rc1=0;
  rc2=h2.check();
  if rc2^=0;
run;

topic Hash table finding key in SAS Programming

Hash table finding key

Re: Hash table finding key