During a run of several steps, I wind up with two copies of the exact same hash table simultaneously in memory. I'd like to just load once and have only one copy of the hash table in memory.
Here's what I'm doing:
1. I compile a subroutine using Proc FCMP. In that subroutine, I declare the hash table and code a find(). I save the compiled subroutine to User.Functions.Hash.
2. Inside a macro, I %SYSCALL the subroutine. I've set my cmplib = User.Functions. I find() three of the records. The first find() takes a minute or so; the hash table is being loaded into memory. The second and third find take less than a second each; the hash table is already loaded. So far, so good.
3. I now execute a DATA step. Again, I've set my cmplib = User.Functions. I find() three of the records -- and the first find() takes a minute or so -- which indicates that the hash table is being loaded (again) into memory. Sadness.
4. Inside a second macro with a different name, I %SYSCALL the subroutine again. This time the first find() takes milliseconds, indicating that the hash table is still in memory.
So, while step 3 is executing, I already have a hash table with millions of rows in it loaded to memory, but SAS loads a second version of the hash table, identical to the first in every respect (they were both created with the same compiled subroutine from the same User.Functions library). I can see that the first hash table remains loaded in memory because subsequent macro calls to the compiled function execute nearly instantaneously, showing that no re-load is going on.
SAS appears to have some way to tell, when called by the second macro, that there's already a hash table in memory. However, in the DATA step, SAS can't seem to tell.
QUESTION: How can I tell SAS "use the hash table already loaded into memory; don't load another copy of the data?"
See code, below. The second macro is essentially the same as the first but with a different name. I'm running on an AIX box with SAS 9.4 M4.
Jim
** Compile a User Defined Subroutine. **;
Proc fcmp outlib=User.Functions.Hash;
subroutine GetStats(name $, height,weight,age) ;
outargs height, weight, age;
declare hash User.class(dataset:'User.big');
rc=User.class.definekey('name');
rc=User.class.definedata('name','height','weight', 'age');
rc=User.class.definedone();
rc=User.class.find();
endsub;
run;
** SYSCALL the Compiled Subroutine. **;
options cmplib=(User.functions);
%macro callsub1(nam=Alfred, n=1);
%local name height weight age;
%let height=.;
%let weight=.;
%let age=.;
%let strt=%sysfunc(datetime());
%LET Current_Time = %SYSFUNC( TIME(), TIME8.);
%PUT NOTE- ;
%PUT NOTE: Starting Macro at &Current_Time;
%do i = 1 %to &n;
%let rand = %sysevalf(%sysfunc(ranuni(987654))*100000,ceil);
%let name = &nam&rand;
%syscall getstats(name,height,weight,age);
/* process utilizing returned values goes here */
%LET Current_Time = %SYSFUNC( TIME(), TIME8.);
%put NOTE- &=name &=age &=Current_Time;
%end;
%let stop=%sysfunc(datetime());
%let time= %sysevalf(&stop-&strt);
%put NOTE- &=n &=height &=weight &=age &=time;
%LET Current_Time = %SYSFUNC( TIME(), TIME8.);
%PUT NOTE: Stopping Macro at &Current_Time;
%PUT NOTE- ;
%mend callsub1;
%callsub1(nam=Barbara, n=3)
** Run a Look Up DATA step that calls the compiled subroutine. **;
&Lookup_Data1 %MACRO Lookup_Data1_Mac();
options cmplib=(User.functions);
data User.lookup1;
retain height weight age .;
DROP _:;
_Time = PUT(TIME(), TIME8.);
PUTLOG "NOTE: Starting Lookup1. " _Time=;
name='Alfred23456';
call getstats(name,height,weight,age);
_Time = PUT(TIME(), TIME8.);
PUTLOG "NOTE- Lookup1 #1 completed. " _Time=;
output;
name='Alice98765';
call getstats(name,height,weight,age);
_Time = PUT(TIME(), TIME8.);
PUTLOG "NOTE- Lookup1 #2 completed. " _Time=;
output;
name='Barbara98765';
call getstats(name,height,weight,age);
_Time = PUT(TIME(), TIME8.);
PUTLOG "NOTE- Lookup1 #3 completed. " _Time=;
output;
run;
Click image to register for webinar
