24704 - Perform multiple lookups for each observation of a SAS data set

Sample 24704: Perform multiple lookups for each observation of a SAS data set

Please note the value used to search the Hash table is changed for each call of the FIND method.

/* Create sample data */

data lookup;
  infile datalines truncover;
  input emp_nbr emp_nm $20.;
datalines;
1000 John Ho
1001 Madhu Sundrie
1002 Jim Simpson
1003 Chris Hostess
2003 Rachel Holiday
2005 Mert Demitter
3004 Amit Gong
;

data group;
  input emp_nbr1 mgr_id mandir_id Account $6. @23 contract_dt mmddyy10.;
  format contract_dt mmddyy10.;
datalines;
1000 2003 3004 ABC Co 05/04/2002
1001 2005 3004 NBC    06/08/2001
1003 2003 3004 XYZ    01/02/2003
;

data final(keep=employee manager mandir account contract_dt);
  length emp_nbr 8;
  length emp_nm $ 20;

  /* On the first iteration of the DATA step, define the Hash object H. */
  /* Use the DATASET: argument tag to load WORK.LOOKUP into H.  The     */
  /* HASHEXP argument tag specifies the number of 'buckets' to use to   */
  /* store the data.  The maximum value for HASHEXP is 20.  You can use */
  /* HASHEXP as a 'tuning knob' that can help with retrieval speed from */
  /* the Hash object.  Benchmark to determine the best size if using    */ 
  /* HASHEXP.  Define the variable (EMP_NBR) that will act as the KEY   */
  /* for the hash lookup and define the data variable (EMP_NM) to be    */
  /* associated with the KEY.  Use CALL MISSING to avoid a note in the  */
  /* log indicating the KEY and DATA variables are uninitialized.       */
  if _N_ = 1 then do;
    declare hash h(dataset: "work.lookup", hashexp: 6);
    h.defineKey('emp_nbr');
    h.defineData('emp_nm');
    h.defineDone();
    call missing(emp_nbr, emp_nm);
  end;

  set group;
  array name(*) $ 20 employee manager mandir;
  array id(*) emp_nbr1 mgr_id mandir_id;

  /* For every element in the array NAME, assign the 'correlated' value from */
  /* the array ID into EMP_NBR.  Use the FIND method to search for that      */
  /* value in the Hash object H.  If a match is located (RC=0), assign the   */
  /* data value from EMP_NM into the specified element in the array NAME.    */
  do i=1 to dim(name);
    emp_nbr=id(i);
    rc=h.find();
    if (rc=0) then name(i)=emp_nm;
  end;
run;

proc print;
run;

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

 
                 contract_
Obs    Account           dt    employee            manager         mandir

 1     ABC Co     05/04/2002    John Ho          Rachel Holiday    Amit Gong
 2     NBC        06/08/2001    Madhu Sundrie    Mert Demitter     Amit Gong
 3     XYZ        01/02/2003    Chris Hostess    Rachel Holiday    Amit Gong

Type:	Sample
Topic:	SAS Reference ==> DATA Step SAS Reference ==> Component Objects SAS Reference ==> Component Objects ==> hash object

Date Modified:	2012-04-02 14:12:50
Date Created:	2004-09-30 14:09:06

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	Base SAS	Tru64 UNIX	9.1 TS1M0	n/a
		OpenVMS Alpha	9.1 TS1M0	n/a
		HP-UX IPF	9.1 TS1M0	n/a
		Linux	9.1 TS1M0	n/a
		64-bit Enabled Solaris	9.1 TS1M0	n/a
		64-bit Enabled HP-UX	9.1 TS1M0	n/a
		64-bit Enabled AIX	9.1 TS1M0	n/a
		Microsoft® Windows® for 64-Bit Itanium-based Systems	9.1 TS1M0	n/a
		z/OS	9.1 TS1M0	n/a

Support

Sample 24704: Perform multiple lookups for each observation of a SAS data set

Operating System and Release Information