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

Note:  When using KEY= it's necessary to test the automatic variable _IORC_.

If a DROP or KEEP is not specified, using the KEY= option combines all variables on the lookup data set and the primary data set. That is, the output data set contains not only the variables from the lookup data set, but also all the variables in the primary data set. This does not happen with KEY= and the MODIFY statement.

PROC SQL can be used as an alternate method to generate the same results. The order of the output may be different. See "Alternate Technique" on the Full Code tab. (This code is similar to Example 3.4 in Combining and Modifying SAS Data Sets - Examples.)

---

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.

/*****************************************************************************/
/* An index is created for data set STORE (master) using the INDEX= data set */
/* option for the key variable ITEM.                                         */
/*                                                                           */
/* An observation is read from the GROCERY_LIST (transaction) data set and   */
/* a matching observation in STORE is located using the index.  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.                        */      
/*****************************************************************************/
                    
data store(index=(Item));
  input Item $ Inventory Aisle $;
datalines;
Milk 15 A
Soymilk 8 A
Eggs 24 A
Cheese 14 A
Bread 12 D
Muffins 8 D
;

data Grocery_List;
  input Item $ Quantity;
datalines;
Bread 2
Milk 1
Butter 1
;

data shop;

  /* Value of KEY variable comes from transaction data set */
  set Grocery_List;

  /* Index used to find matches for KEY= value */
  set store key=Item;

  /* Check return code from search */
  select (_iorc_); 

    /* Match found */                                  
    when (%sysrc(_sok)) do;
      output;
    end;
 
    /* Match not found in master */  
    when (%sysrc(_dsenom)) do;
      _ERROR_=0;
      /* Reset values in PDV from last matched condition */
      inventory=.;
      Aisle='N/A';
      output;
    end;

    otherwise do;                                          
      put 'Unexpected ERROR: _iorc_= ' _iorc_;  
      stop;                                    
    end; 
  end; 
run;

proc print data=shop;
run;

/* Alternative Technique  -- Same output results, but the observation order */
/*                           may be different                               */

/* This is a left outer join, which returns rows that satisfy the condition */
/* in the ON clause.  In addition, a left outer join returns all the rows   */
/* from the left table (first table listed in the FROM clause) that do not  */
/* match with a row from the right table (second table listed in the FROM   */
/* clause).                                                                 */

proc sql;                                                                         
  create table shop2 as                                                          
    select Grocery_List.Item, Quantity, Inventory, 
      coalesce(Aisle,"N/A") as Aisle 
        from Grocery_List left join store                                         
          on Grocery_List.Item=store.Item;                                        
quit;

---

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.

Obs     Item     Quantity    Inventory    Aisle

 1     Bread         2           12        D
 2     Milk          1           15        A
 3     Butter        1            .        N/A

Operating System and Release Information