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
Type: | Sample |
Topic: | SAS Reference ==> DATA Step SAS Reference ==> Statements ==> File-handling ==> SET Common Programming Tasks ==> Combining Data SAS Reference ==> Statements ==> File-handling ==> SET ==> with KEY= |
Date Modified: | 2008-01-28 11:01:47 |
Date Created: | 2004-09-30 14:09:02 |
Product Family | Product | Host | SAS Release | |
Starting | Ending | |||
SAS System | Base SAS | All | n/a | n/a |