topic Re: Convert Hash Table code to SQL in SAS in SAS Programming

Convert Hash Table code to SQL in SAS

rahulsaha2127 — Thu, 19 Jan 2023 19:42:55 GMT

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.

Re: Convert Hash Table code to SQL in SAS

SASKiwi — Thu, 19 Jan 2023 19:48:01 GMT

There's no equivalent in SQL.

Re: Convert Hash Table code to SQL in SAS

PeterClemmensen — Thu, 19 Jan 2023 19:51:34 GMT

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;

Re: Convert Hash Table code to SQL in SAS

Sajid01 — Fri, 20 Jan 2023 03:54:30 GMT

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 .

Re: Convert Hash Table code to SQL in SAS

rahulsaha2127 — Fri, 20 Jan 2023 08:49:08 GMT

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;

Re: Convert Hash Table code to SQL in SAS

LinusH — Fri, 20 Jan 2023 08:50:28 GMT

Given that you have working code - why?

Re: Convert Hash Table code to SQL in SAS

Kurt_Bremser — Fri, 20 Jan 2023 08:57:54 GMT

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.