DATA Step, Macro, Functions and more

Hash table finding key

Reply
Contributor
Posts: 67

Hash table finding key

[ Edited ]

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.

Trusted Advisor
Posts: 1,311

Re: Hash table finding key

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;
Ask a Question
Discussion stats
  • 1 reply
  • 93 views
  • 0 likes
  • 2 in conversation