Convert Hash Table code to SQL in SAS

[rahulsaha2127]

I have a code leveraging SAS Hash Tables for join and I want to create  the same table using Procedure SQL.

Here is the following HASH Table code:

data tem_excludes;
if _n_ eq 1 then do;
declare hash sec (dataset:"d_sec_list");
sec.definekey ('curr_obr_num', 'sys', 'fc_num');
sec.definedata ('secure_exclusion');
sec.definedone ();
call missing(secure_exclusion);
declare hash sng (dataset:"single_entry");
sng.definekey ('fackey');
sng.definedata ('count');
sng.definedone ();
call missing(count);
end;

set tem_exclude0;
rc = sec.find();
rc = sng.find();

if secure_exclusion then p_flag= 'Y';
if count EQ 1 then do;
p_flag= 'Y';
single_entry = 1;
end;
else single_entry = 0;

run;

Would need to write an equivalent code using Proc sql without any hash table.

Please share your inputs.

[SASKiwi]

There's no equivalent in SQL.

[PeterClemmensen]

There is no direct equvalent i Proc SQL. However, this should get you some of the way with the lookup / join part.

proc sql;

   create table tem_excludes as
   
   select a.*
        , b.secure_exclusion
		, c.count
		
   from tem_exclude0 as a
   
   left join d_sec_list as b
   on  a.curr_obr_num = b.curr_obr_num
   and a.sys          = b.sys
   and a.fc_num       = b.fc_num
   
   left join single_entry as c
   on a.fackey = c.fackey
   
   ;
quit;

[rahulsaha2127]

After the join using the hash table for which you have provided the sql alternative, what is the code exactly trying to do? Is it creating new variables on the newly created dataset tem_excludes?
Am referring to this part:
if secure_exclusion then p_flag= 'Y';
if count EQ 1 then do;
p_flag= 'Y';
single_entry = 1;
end;
else single_entry = 0;

run;

[Sajid01]

The SAS hash object / Table is available in a data step only.
While one can always Proc SQL to join two tables to get an identical outcome, the two process work differently .

[LinusH]

Given that you have working code - why?

[Kurt_Bremser]

Hash objects are typically used to MASSIVELY speed up a join process. Converting this to SQL code will probably end up with you watching the paint dry or the grass grow.

Don't do it, unless you must because the hash objects cause a memory overflow.