Hash table search for multiple columns in SAS

[windlove]

Thanks so much. The speed is further improved. The first time run was about 28 mins, and the second time run was about 17 mins. 

However, do you mind telling me what's the purpose of the slash '|' before '~mh.check()'?

[ChrisNZ]

| is the OR operator written differently.

[windlove]

What I meant is , how does this code work, why OR here, why not IF?

[ChrisNZ · Accepted solution]

Back at the computer now. I did a quick test and for me, the IF OR method is about twice as slow as the DO  loop.

data HAVE MH_CODES(keep=COL25 rename=(COL25=CODES));
  array COL[1:50];
  do I=1 to 1e4;
    do C=1 to 50;
      COL[C]=I*C;
      output HAVE;
      if ranuni(1)>.5 then output MH_CODES;
     end;
  end;
run;
      
data WANT;   *  6.28 seconds;
  array COLS [*] COL1 - COL50;
  if _N_ = 1 then do;
      declare hash MH(dataset: 'MH_CODES');
      MH.defineKey('CODES');
      MH.defineDone();
      call missing(CODES);
   end;
   do until(LAST);
      set HAVE end = LAST;
      if    %macro loop; %local i; %do i = 1 %to 50;
        ~MH.check(key: COLS[&i])  OR   
            %end; %mend; %loop      
        0 then output;
   end;
run;

data WANT;   *  3.69 seconds;
  array COLS [*] COL1 - COL50;
  if _N_ = 1 then do;
    declare hash MH(dataset: 'MH_CODES');
    MH.defineKey('CODES');
    MH.defineDone();
    call missing(CODES);
  end;
  do until(LAST);
    set HAVE end = LAST;
    do I = 1 to 50;
      if ~MH.check(key: COLS[I]) then do;
        output;
        leave;
      end;  
    end;  
  end;
run;

I had and extraneous leave statement in my code, which I removed. This might explain your different results. Please check again.

Using check() should definitely save time though.

[windlove]

Hi Chris, thanks for the further help on this. I am working on a remote server, so normally during the business hours, the server could be very busy. Applying the codes on my data, the earlier codes with %macro took about 25 mins to complete the subsetting, and the new codes  with "IF" statement without %macro took about 38 mins. Offcoz it is also based on the server performance at the moment of running. 

However, the earlier codes only took about 4 mins to complete the task during off peak hours last night. It is remarkable I would say. Thanks very much for your help. I will accept your latest response as the solution. 

[ChrisNZ]

From 2 hours to 4 minutes is a massive improvement. Well done!

Good on you for tuning your program. Performance matters.