Simple Table Lookup (Method 1 - Merging) - SAS Programming

Suppose you have a table of part numbers and descriptions in a SAS data set called PARTS. (Assume this data set is already sorted by PART_NO.)You want to read a part number from a data set called INVOICE and add the variable PARTNAME from the PARTS data set to your INVOICE data set. First, here are the two data sets: You can use a MERGE statement to perform the lookup as follows:

Simple Table Lookup (Method 1 - Merging)

Example

PROC SORT DATA=INVOICE;
BY PARTJiO;
RUN;
DATA LOOKUP1;
MERGE INVOICE {IN"INCLUDE) PARTS;
BY PART_NQ;
IF INCLUDE = 1;
IF PARTNAME « ' ' THEN PARTNAME * 'NOT ENTERED';
RUN;
PROC PRINT DATA«LOOKUP1;
TITLE 'Resulting File';
RUN;

Output from the PRINT procedure is shown next:

Output From Example-Simple Table Lookup (Method 1 - Merging)

Output From Example-Simple Table Lookup (Method 1 - Merging)

This program merges the two SAS data sets,PARTS and INVOICE,using the variable PART_NO as the BY variable.Notice that we first sort the INVOICE data set by PART_NO since it is necessary that both data sets be sorted by the same variable.

The PARTS data set was previously sorted,and there is no sense in wasting CPU cycles in sorting it again.(Remember that in SAS System Releases 6.07 and higher,a sort flag is set when a data set is sorted, and a redundant SORT procedure will not be executed.)

The IN= option assures you that only invoiced items (those in the INVOICE data set)will be kept.(See Chapter 3,"SET, MERGE,and UPDATE,"for a more detailed explanation of the MERGE
statement.) To denote the case where a PART_NO value in the INVOICE data set is not in the PARTS data set, you substitute the term NOT ENTERED for your missing value.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SAS Programming Topics