@rowlinglu:
" why decreasing in variable length can make hash item jump to the next increment?"
I don't think @ChrisNZ has said anything of the kind.
Judging from you last few posts, you must've developed certain misconceptions with regard to what I've named L and S, so:
- L is the summary system length, in bytes, of the PDV host variables corresponding to the hash variables defined for the hash table
- S is the number of memory bytes SAS actually uses for the hash table entry composed of the hash variables
Suppose we have the hash entry defined as:
h.defineKey ("Kc") ;
h.defineData ("Dc") ;
Let's assume that the host variables for the hash variables Kn and Dc are both defined (at compile time) as $1. Then L=1+1=2 bytes. In this case, on a 64-bit system,the hash object will grab S=48 bytes for the hash entry. If you keep increasing the length of either variable (or both), S will be kept at S=48 while length(Kc)+length(Dc)<=16, but at L=17 will become 64. However, L is not the only factor affecting S since, in addition to L, it also depends on:
- The number of hash variables N: In general, the more variables (at the same L), the greater S can be. This kind of stands to reason, as the more hash variables you have, the more memory overhead is needed to keep track of them in the underlying AVL trees.
- Variable data type T: In some cases (but not always), a numeric variable (8 bytes) increase S more than a character variable $8.
In sum, the behavior of the function S=F(L, N, T) is very hard to predict - if not impossible since as yet, I haven't seen anyone formulate its exact rules. What can be said about F definitively is that it's always a non-decreasing step-wise function of L and N; and as a rule of thumb, the shorter is L and the smaller is N, the smaller is the resulting S.
Also, the dependency on N, given the rest of the factors fixed, is quite strong. For example, if you define the key portion with 16 $1 variables K1-K16 and the data portion with a single $16 variable, i.e. with L=32, SAS will set S=176 (sic), with 176-32=144 bytes memory overhead per hash item. But if you concatenate K1-K16 into a single key K $16 (leaving the data portion intact), it will set S=64, with only 64-32=32 bytes of memory overhead per item. That's 144-32=112 fewer memory bytes wasted per item, which for a hash table with 10 million items saves over 1G of otherwise completely wasted memory.
It nicely dovetails into the idea of saving hash memory by using MD5 to replace the entire key portion containing many hash variables, i.e. a multi-component compound key, with a single 16-byte key. Per above, its benefits include:
- Reducing the key portion summary length to 16 bytes regardless of the number and lengths of the original key components (as @DonH has already noted). Thus, the resulting L will always be only 16 plus the total length of the data portion variables.
- Reducing N to 1 regardless of N.
- Doing away with any effect numeric key components may have on S, as all of them are thus eliminated.
If all of these factors are present, they have a cumulative effect on the key portion's hash memory footprint; and the only expense is the need to concatenate the key components using CATX or CATQ and compute MD5 (albeit twice: at the time when the table is loaded and then when it is searched).
Now as far as the data portion is concerned, the MD5 method cannot be used to reduce the data portion memory footprint for the obvious reason that MD5 is a one-way hash function, and its argument cannot be reconstructed from its MD5 digest. However, you're dealing with a simple join, in which case the entire data portion can be reduced to a single numeric variable. To wit, instead of storing all the satellite variables from the file being joined in the table, you can store only their RID (record identification) number. Then on the search side, if a key is found, you can just use the POINT=RID option to retrieve the values of all the satellite variables you need into the PDV. This method is described in detail, for example, here:
https://www.lexjansen.com/nesug/nesug11/ld/ld01.pdf
In your specific case, it would mean (I'm using @ChrisNZ's code as a template):
data master_inpatient ;
input iID (iadmsn_dt ithru_dt) (:yymmdd10.) iprvdr_num $1. ;
format iadmsn_dt ithru_dt yymmdd10. ;
cards ;
341 2013-04-01 2013-04-02 G
230 2013-06-01 2013-06-03 I
232 2013-07-31 2013-07-31 F
124 2013-04-29 2013-04-29 C
232 2013-07-31 2013-08-20 Q
run ;
data filtered_outpatient ;
input ID thru_dt :yymmdd10. prvdr_num $1. ;
format thru_dt yymmdd10. ;
cards ;
124 2013-04-29 A
331 2013-06-14 G
439 2013-02-01 B
331 2013-06-14 D
run ;
data want ;
if _n_ = 1 then do ;
dcl hash h (multidata:"y");
h.definekey ("id") ;
h.definedata ("rid") ;
h.definedone () ;
do rid = 1 by 1 until (z) ;
set filtered_outpatient end = z ;
h.add() ;
end ;
end ;
set master_inpatient (rename = iID = ID) ;
if h.find() = 0 then do until (h.find_next() ne 0) ;
set filtered_outpatient (keep = thru_dt prvdr_num) point = rid ;
dif = abs (iadmsn_dt - thru_dt) ;
if dif <= 1 then output ;
end ;
run ;
Note that since you have only 2 satellite variables here (thru_dt and prfdr_num), using this trick won't win you any prizes. But if, as you say, you have to deal with about 20 satellite variables to retrieve, it will make the code much more memory-savvy without any practical diminution in speed since (a) POINT= is actually very fast and (b) by the nature of the algorithm here, it accesses the records by their obs number in order.
For rendering joins using the hash object, if you have a compound key with numerous components and many data variables to retrieve, the MD5 method can be used on the key portion side and the RID method - on the data portion side. You can find examples of using both separately and combined in gory details in the hash book @DonH and I published a year ago in SAS Press, an excerpt from which you can preview at:
http://www.sas.com/storefront/aux/en/sphashtables/69153_excerpt.pdf
Kind regards
Paul D.
Click image to register for webinar
