Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

This sample demonstrates two methods for adding values from datasets MASTER (indexed on PartNumber) and DESCRIPTION (indexed on PartNumber) to ORDER based upon the key variable PartNumber.

Note:  Error checking in the random access method must be done on all statements that use the KEY= option. The automatic variable _IORC_ is tested using the mnemonics defined in the SAS-supplied %SYSRC autocall macro. The value _SOK means there was a match found in the master data set, and the value _DSENOM means a match was not found.

Note:  Error checking for the hash table method is done by testing the value of RC. The hash table sample does not require the data sets to be indexed in the code shown. However, the look up data sets must be small enough to fit into the real memory available to the SAS session.

Hash tables are stored in real memory, not virtual memory.

---

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.

data master(index=(PartNumber));
  input PartNumber quantity;
datalines;
100 10
200 20
300 30
400 40
500 50
;

data description(index=(PartNumber));
  input PartNumber partdescription $;
datalines;
400          Nuts
300          Bolts
200          Screws
600          Washers
;

data order;
  input PartNumber;
datalines;
200
400
100
300
800
500
600
;

data combine(drop=found); 
  length PartDescription $ 15;

  /* Read observation from ORDER */
  set order; 

  /* Found's value will be used later to indicate */
  /* when a PartNumber value has a match in the   */
  /* DESCRIPTION data set.                        */
  found = 0; 

  /* Read an observation from DESCRIPTION based */
  /* upon current value of PartNumber           */ 
  set description key=PartNumber;   
  select(_iorc_); 

    /* Match found */ 
    when(%sysrc(_sok)) do;
      found = 1;
    end;

    /* No match found */
    when(%sysrc(_dsenom)) do;
      PartDescription = 'No description';
      _error_ = 0;
    end;
    
    otherwise do;
      put 'ERROR: Unexpected value for _IORC_= ' _iorc_;
      put 'Program terminating. Data set accessed is DESCRIPTION';
      put _all_;
      _error_ = 0;
      stop;
    end;
  end;

  /* Read in observation from MASTER based upon */
  /* current value of PartNumber                */                     
  set master key=PartNumber;  
  select(_iorc_); 

    /* Match found */
    when(%sysrc(_sok)) do;
      output;
    end;

    /* No match found */
    when(%sysrc(_dsenom)) do;
      /* and if match was not found in DESCRIPTION either */
      if not found then do;
        _error_ = 0;
        put 'WARNING: PartNumber ' PartNumber 'is not in'
            ' DESCRIPTION or MASTER.';
      end;
      
      else do;
        Quantity = 0;
        _error_ = 0;
        output;
      end;
    end;
    
    otherwise do;
      put 'ERROR: Unexpected value for _IORC_= ' _iorc_;
      put 'Program terminating. Data set accessed is MASTER';
      put _all_;
      _error_ = 0;
      stop;
    end;
  end;      /* Ends the SELECT group */ 
run;

proc print data=combine;
title 'Combination using Random Access';
run;

/* combine data sets using hash tables for look up */
data combine_2 (drop=found rc); 
  length PartDescription $ 15 quantity 8;

  if _N_ = 1 then do;
    declare hash master_ht(dataset:"master",ordered:"y");
    master_ht.defineKey("PartNumber");
    master_ht.defineData("quantity");
    master_ht.defineDone();
   
    declare hash descr_ht(dataset:"description",ordered:"y");
    descr_ht.defineKey("PartNumber");
    descr_ht.defineData("partdescription");
    descr_ht.defineDone();
    call missing(quantity, partdescription);
  end;


  /* Read observation from ORDER */
  set order;

  /* Found's value will be used later to indicate */
  /* when a PartNumber value has a match in the   */
  /* DESCRIPTION data set.                        */
  found = 0; 

  /* look up Description */
  rc = descr_ht.find(key:PartNumber);
  if rc = 0 then 
    found = 1;
  else do;  
    PartDescription = 'No description';
  end;

  /* look up on master */
  rc = master_ht.find(key:PartNumber);
  if rc = 0 then  
    output;
  else do; 
    if not found then do;
      _error_ = 0;
      putlog 'WARNING: PartNumber ' PartNumber 'is not in'
            ' DESCRIPTION or MASTER.';
    end;      
    else do;
      Quantity = 0;
      _error_ = 0;
      output;
    end;
  end;
run;

proc print data=combine_2;
title 'Combination using hash tables';
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.

Both methods give identical results, shown below.

       Part
Obs    Description       partnumber    quantity

 1     Screws                200          20
 2     Nuts                  400          40
 3     No description        100          10
 4     Bolts                 300          30
 5     No description        500          50
 6     Washers               600           0

Operating System and Release Information