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

If you want to append values in one data set to the end of another data set, you can either use PROC APPEND, or use a SET statement that lists the names of the data sets you want to combine.

However, what if the two data sets have the same variable names, but some of the character variables have different lengths in the two data sets? Furthermore, the character variables stored with longer lengths are not consistent in one or the other data set.

You cannot use PROC APPEND because that procedure will use all the attributes from the base data set, which will truncate any character values that are longer in the second data set.

You can use a SET statement, but you will have to manually enter the length of each character variable (in a LENGTH statement) from either data set one or data set two, whichever is longer.

Because this is a fairly common problem, there is a macro that combines two data sets and automatically uses the maximum length for each character variable.

Here is the macro (followed by an explanation):

%macro union(dsn1=,     /*Name of the first data set    */
             dsn2=,     /*Name of the second data set   */
             out=       /*Name of combined data set     */);
   
   proc contents data=&dsn1 noprint 
      out=out1(keep=name type length where=(type=2));

   proc contents data=&dsn2 noprint 
      out=out2(keep=name type length where=(type=2));
   run;

   data _null_;
      file "combined.sas";
      merge out1 out2(rename=(length=length2)) end=last;
      by name;
      if _n_ = 1 then put "Data &out;";
      l = max(length,length2);
      put "   length " name " $ " l 2. ";";
      if last then do;
         put "   set &dsn1 &dsn2;";
         put "run;";
      end;
   run;

   %include "combined.sas";

%mend union;

Although there are many methods for determining information on the variables in your data set (variable information functions, library tables, etc.), this program uses PROC CONTENTS to output a data set that contains information on the character variables in each of the two data sets. The WHERE= data set option selects the character variables from each data set.

To see more clearly how this program works, we created two small test data sets (called ONE and TWO). Here are the listings of the two data sets produced by PROC CONTENTS:

Listing of Data Set ONE

Obs    NAME    TYPE    LENGTH

 1      c1       2        4
 2      c2       2        8

Listing of Data Set TWO

Obs    NAME    TYPE    LENGTH

 1      c1       2        3
 2      c2       2       10

Notice that the length of C1 is longer in the first data set and the length of C2 is longer in the second data set.

The DATA _NULL_ step starts by writing out a DATA statement to an external file. This is followed by a LENGTH statement that uses the maximum length for each variable. Finally, a SET statement lists the two data sets, followed by a RUN statement. A %INCLUDE statement then runs the SAS code that was produced.

Below is a listing of the COMBINED.SAS program created in this data step (using the two test data sets ONE and TWO):

Data union;
   length c1  $  4;
   length c2  $ 10;
   set one two;
run;

To see that this worked as advertised, here is part of the output from PROC CONTENTS on the resulting data set (UNION):

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

1    c1          Char      4
2    c2          Char     10
3    x           Num       8
4    y           Num       8

This sample was authored by Ron Cody. His books:

• Applied Statistics and the SAS Programming Language, Fifth Edition
• Cody's Data Cleaning Techniques Using SAS, Second Edition
• Learning SAS by Example: A Programmer's Guide
• Longitudinal Data and SAS: A Programmer's Guide
• SAS Functions by Example
• SAS Programming by Example
• The SAS Workbook and Solutions Set

---

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.

---

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.

Operating System and Release Information