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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.