/*****************************************************************************************************************
 
SAS file name: partial_key_hash.sas
File location: 
__________________________________________________________________________________________________________________
 
Purpose: To demonstrate how to do a partial-key lookup in a multi-key SAS hash object
Author: Peter Clemmensen
Creation Date: 
 
This program supports the blog post "Partial Key Lookup in SAS Hash Object" on SASnrd.com

*****************************************************************************************************************/

/* Example Data */
data have;
input x $ y z v;
datalines;
A 1 1 100
A 1 2 200
A 2 3 300
A 2 4 400
B 3 5 500
B 3 6 600
B 4 7 700
B 4 8 800
;

/* Wishful syntax */
data _null_;
   if 0 then set have;

   dcl hash h(dataset : 'have', multidata : 'Y');
   h.definekey('x', 'y');
   h.definedata(all : 'Y');
   h.definedone();

   rc = h.find(key : "B");
run;

/* Keys: X and Y - Lookup all items for X = "B" */
data _null_;
   if 0 then set have;

   dcl hash h(dataset : 'have', multidata : 'Y');
   h.definekey('x', 'y');
   h.definedata(all : 'Y');
   h.definedone();

   dcl hash u(dataset : 'have', ordered : 'Y');
   u.definekey('x', 'y');
   u.definedone();
   dcl hiter ui('u');

   dcl hash hx(multidata : 'Y');
   hx.definekey('x');
   hx.definedata('y');
   hx.definedone();

   do while (ui.next() = 0);
      hx.add();
   end;

   x = "B";
   do while (hx.do_over() = 0);
      do while (h.do_over() = 0);
         put (x y z v) (=);
      end;  
   end;

run;

/* Keys: X, Y and Z - Lookup all items for X = "B" and Y = 3 */
data _null_;
   if 0 then set have;

   dcl hash h(dataset : 'have', multidata : 'Y');
   h.definekey('x', 'y', 'z');
   h.definedata(all : 'Y');
   h.definedone();

   dcl hash u(dataset : 'have', multidata : 'Y', ordered : 'Y');
   u.definekey('x', 'y', 'z');
   u.definedone();
   dcl hiter ui('u');

   dcl hash hxy(multidata : "Y");
   hxy.definekey("x", "y");
   hxy.definedata("z");
   hxy.definedone();

   do while (ui.next() = 0);
      hxy.add();
   end;

   x = "B"; y = 3;
   do while (hxy.do_over() = 0);
      do while (h.do_over() = 0);
         put (x y z v) (=);
      end;  
   end;

run;