Efficiently combine two data sets when one is a large data set whose retrieved values remain fairly constant.

Note: This is Example 3.9 from "Combining and Modifying SAS Data Sets: Examples First Edition". PROC SQL can also be used to accomplish this task. See the sample under "Related Technique" on the "Full Code" tab.

For SAS 9.0 and above, see Sample 24768 which illustrates using a hash component object as a lookup technique.

---

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.

---

/* Create sample data sets */

data lookup; 
   input partno $ desc $15.;
   datalines;
A401 tuning peg
A025 bridge
A203 nut
A220 neck
A810 pick guard
A063 pickup
A047 pot
A608 volume knob
A097 toggle switch
A498 body
;

data primary;
   input partno $ quantity;
   datalines;
A220 4
A498 4
A063 8
A810 4
;

/* Create a data set to pass information from the lookup file        */
/* to the FORMAT procedure to dynamically build the format.          */
/* Specify this data set in the CNTLIN=option as input to the        */
/* FORMAT procedure.  PROC FORMAT uses the data in the input control */
/* data set to build the format.                                     */

data formats;
   set lookup(rename=(partno=start desc=label));
   fmtname='$parts';
run;

proc format cntlin=formats;
run;

/* Create a new data set by reading observations from the primary   */
/* file and using the PUT function to apply the formatted values    */
/* of the common variable to a new variable.                        */

data report1;
   set primary;
   desc=put(partno,$parts.);
run;

proc print data=report1;
   title 'REPORT1';
run;


/* Related technique using SQL --  If you are familiar with SQL, you may want    */
/* to use PROC SQL instead of the DATA step.  Using the PRIMARY table,           */
/* PROC SQL creates a new table, REPORT2, that has a new column DESC.  The PUT   */
/* function assigns values to DESC by using the $PARTS. format, created earlier, */
/* with the PARTNO column.  The $PARTS. format contains a description for each   */
/* part represented in the PARTNO column.                                        */

proc sql;
   create table report2 as
      select *, put(partno,$parts.) as desc
         from primary;
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    partno    quantity    desc

 1      A220         4       neck
 2      A498         4       body
 3      A063         8       pickup
 4      A810         4       pick guard

Efficiently combine two data sets when one is a large data set whose retrieved values remain fairly constant.

Type:	Sample
Topic:	SAS Reference ==> DATA Step SAS Reference ==> Procedures ==> SQL SAS Reference ==> Procedures ==> FORMAT

Date Modified:	2012-04-13 12:58:06
Date Created:	2004-09-30 14:09:05

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	All	n/a	n/a