/*****************************************************************************************************************
 
SAS file name: count_distinct_hash.sas
File location: 
__________________________________________________________________________________________________________________
 
Purpose: To demonstrate how to count distinct values in SAS using the hash object.
Author: Peter Clemmensen
Creation Date: 
 
This program supports the blog post "Count Distinct Values in SAS With the Hash Object" on SASnrd.com

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

/* Example data */
data have;
input id x;
datalines;
1 1
1 2
1 2
2 3
2 3
3 4
3 5
3 6
;

/* Two hash objects */
data _null_;
   dcl hash h (ordered: "A");
   h.definekey ("id");
   h.definedata ("id", "unq");
   h.definedone ();

   dcl hash u ();
   u.definekey ("id", "x");
   u.definedone ();

   do until (z);
      set have end = z;
      if h.find() ne 0 then call missing (unq);
      if u.check() ne 0 then do;
         unq = sum (unq, 1);
         u.add();
      end;
      h.replace();
   end;

   h.output (dataset: "count_distinct");
   stop;
run;

/* Hash of Hashes */
data want(keep=id unq);
 
   dcl hash hoh (ordered : "Y");
   hoh.definekey("id");
   hoh.definedata("h", "id");
   hoh.definedone();
   dcl hiter i ("hoh");
 
   dcl hash h;
 
   do until (lr);
      set have end=lr;
      if hoh.find() ne 0 then do;
         h = _new_ hash (multidata : "Y");
         h.definekey ("id", "x");
         h.definedone();
         hoh.add();
      end;
      h.ref();
   end;
 
   do while (i.next() = 0);
      unq = h.num_items;
      output;
   end;
run;

/* Similar to the PROC SQL below */
proc sql noprint;
   create table want as
   select id, 
          count(distinct x) as unq
   from have
   group by id;
quit;