Sometimes, you need to combine data from two or more sets into a single observation in a new data set according to the values of a common variable. When the data sources are two or more data sets that have a common structure, you can use a match-merge to combine the data sets. However, in some cases the data sources do not share a common structure. When data sources do not have a common structure, you can use a lookup table to match them. A lookup table is a table that contains key values.

The technique that you use to perform a table lookup is dependent on your data. This chapter focuses on using multidimensional arrays to perform table lookups and transposing SAS data sets in preparation for a match-merge.

In this chapter, you learn to

When a lookup operation depends on more than one numerical factor, you can use a multidimensional array. You use an ARRAY statement to create an array. The ARRAY statement defines a set of elements that you plan to process as a group.

When you are working with arrays, remember that

Suppose you need to determine the wind chill values for the flights represented in the SAS data set Sasuser.Flights. The data set contains three variables: Flight (the flight number), Temp (the average outdoor temperature during the flight), and Wspeed (the average wind speed during the flight).

Figure 16.1. SAS Data Set Sasuser.Flights

Wind chill values are derived from the air temperature and wind speed as shown in the following wind chill lookup table. To determine the wind chill for each flight, you can create a multidimensional array that stores the wind chill values shown in the table. You can then match the values of Temp and Wspeed with the wind chill values stored in the array.

Figure 16.2. Temperature (in degrees Fahrenheit)

In the following program, the ARRAY statement creates the two-dimensional array WC and specifies the dimensions of the array: four rows and two columns. No variables are created from the array because the keyword _TEMPORARY_ is used. The initial values specified correspond to the values in the wind chill lookup table. For this example, only the values in the first two columns and four rows in the wind chill lookup table are included in the array.

data work.wndchill (drop = column row);
  array WC{4,2} _temporary_ 

          (-22,-16,-28,-22,-32,-26,-35,-29); 
  set sasuser.flights;
  row = round(wspeed,5)/5;
  column = (round(temp,5)/5)+3;
  WindChill= wc{row,column};
run;

Figure 16.3. Temperature (in degrees Fahrenheit)

The value of WindChill for each flight is determined by referencing the array based on the values of Wspeed and Temp in the Sasuser.Flights data set. The row number for the array reference is determined by the value of Wspeed. The column number for the array reference is determined by the value of Temp.

The rounding unit for the value of Wspeed is 5 because the values for wind speed in the wind chill table are rounded to every 5 miles-per-hour. Wspeed is then divided by 5 to derive the row number for the array reference.

Like the value for Wspeed, the value of Temp is rounded to the nearest 5, then divided by 5. The offset of 3 is added to the value because the third column in the wind chill lookup table represents 0 degrees.

data work.wndchill (drop = column row);
  array WC{4,2} _temporary_
          (-22,-16,-28,-22,-32,-26,-35,-29);
  set sasuser.flights;
  row = round(wspeed,5)/5; 
  column = (round(temp,5)/5)+3; 

  WindChill= wc{row,column};
run;

PROC PRINT output shows the completed data set.

Suppose you want to compare the actual cargo revenue values in the SAS data set Sasuser.Monthsum to the target cargo revenue values in the SAS data set Sasuser.Ctargets.

Sasuser.Monthsum contains the actual cargo and passenger revenue figures for each month from 1997 through 1999.

The SAS data set Sasuser.Ctargets contains the target cargo revenue figures for each month from 1997 through 1999.

You want to create a new SAS data set, Work.Lookup1, that lists the actual and target values for each month. Work.Lookup1 should have the same structure as Sasuser.Monthsum: an observation for each month and year, as well as a new variable, Ctarget (target cargo revenues). The value of Ctarget is derived from the target values in Sasuser.Ctargets.

Sasuser.Monthsum and Sasuser.Ctargets cannot be merged because they have different structures:

However, the data sets have two common factors: month and year. You can use a multidimensional array to match the actual values for each month and year in Sasuser.Monthsum with the target values for each month and year in Sasuser.Ctargets.

The first step is to create an array to hold the values in the target data set, Sasuser.Ctargets. The array needs two dimensions: one for the year values and one for the month values. In the following program, the first ARRAY statement creates the two-dimensional array, Targets.

Remember that the index of an array does not have to range from one to the number of elements. You can specify a range for the values for the index when you define the array. In this case, the dimensions of the array are specified as three rows (one for each year: 1997, 1998, and 1999) and 12 columns (one for each month).

data work.lookup1;
  array Targets{1997:1999,12} _temporary_; 
  if _n_=1 then do i= 1 to 3;
          set sasuser.ctargets;
          array mnth{*} Jan--Dec;
          do j=1 to dim(mnth);
             targets{year,j}=mnth{j};
          end;
    end;
    set sasuser.monthsum(keep=salemon revcargo monthno);
    year=input(substr(salemon,4),4.);
    Ctarget=targets{year,monthno};
    format ctarget dollar15.2;
run;

The following table represents the Targets array. Notice that the array is not populated. The next step is to load the array elements from Sasuser.Ctargets.

array Targets{3,12} _temporary_;

However, using the notation 1997:1999 simplifies the program by eliminating the need to map numeric values to the year values.

The Targets array needs to be loaded with the values in Sasuser.Ctargets. One method for accomplishing this task is to load the array within a DO loop.

The IF-THEN statement specifies that the Targets array is loaded only once, during the first iteration of the DATA step. The DO loop executes three times, once for each observation in Sasuser.Ctargets.

The ARRAY statement within the DO loop creates the Mnth array, which will be used to store the elements from Sasuser.Ctargets. The dimensions of the Mnth array are specified using an asterisk, which enables SAS to automatically count the array elements.

The array elements Jan through Dec are listed using a double hyphen (- -). The double hyphen (- -) is used to read the specified values based on their positions in the PDV instead of alphabetically.

data work.lookup1;
  array Targets{1997:1999,12} _temporary_;
  if _n_=1 then do i= 1 to 3; 
          set sasuser.ctargets; 
          array Mnth{*} Jan--Dec; 
          do j=1 to dim(mnth);
             targets{year,j}=mnth{j};
          end;
  end;
  set sasuser.monthsum(keep=salemon revcargo monthno);
  year=input(substr(salemon,4),4.);
  Ctarget=targets{year,monthno};
  format ctarget dollar15.2;
run;

The following table shows the values in the Mnth array after the first iteration of the DO loop.

Within the nested DO loop, the Targets array reference is matched to the Mnth array reference in order to populate the Targets array. The DIM function returns the number of elements in the Mnth array (in this case 12) and provides an ending point for the nested DO loop.

data work.lookup1;
  array Targets{1997:1999,12} _temporary_;
  if _n_=1 then do i= 1 to 3;
          set sasuser.ctargets;
          array Mnth{*} Jan--Dec;
          do j=1 to dim(mnth); 
             targets{year,j}=mnth{j}; 
          end;
  end;
  set sasuser.monthsum(keep=salemon revcargo monthno);
  year=input(substr(salemon,4),4.);
  Ctarget=targets{year,monthno};
  format ctarget dollar15.2;
run;

The last step is to read the actual values stored in Sasuser.Monthsum. Remember that you need to know the month and year values for each observation in order to locate the correct target revenue values.

The values for month are read in from MonthNo. The year values are contained within the values of SaleMon and can be extracted using the SUBSTR function. In this example, the SUBSTR function brings in four characters from SaleMon, starting at the fourth character. Note that the INPUT function is used to convert the value that is extracted from SaleMon from character to numeric in the assignment statement for Year. A numeric format must be used because the value of Year will be used as an array reference.

The values of Ctarget are then read in from the Targets array based on the value of Year and MonthNo.

data work.lookup1;
  array Targets{1997:1999,12} _temporary_;
  if _n_=1 then do i= 1 to 3;
          set sasuser.ctargets;
          array Mnth{*} Jan--Dec;
          do j=1 to dim(mnth);
             targets{year,j}=mnth{j};
          end;
  end;
  set sasuser.monthsum(keep=salemon revcargo monthno); 
  year=input(substr(salemon,4),4.); 
  Ctarget=targets{year,monthno}; 
  format ctarget dollar15.2;
run;

PROC PRINT output shows the new data set Work.Lookup1, which contains the actual cargo values (RevCargo) and the target cargo values (Ctarget).

The following program transposes the SAS data set Sasuser.Ctargets. The OUT=option specifies the name of the output data set, Work.Ctarget2. All of the variables in Sasuser.Ctargets are transposed because all of the variables are numeric and a VAR statement is not used in the program.

proc transpose data=sasuser.ctargets
     out=work.ctarget2; 
run;

Notice that in the output data set, the variables are named _NAME_, COL1, COL2, and COL3.

_NAME_ is the default name of the variable that PROC TRANSPOSE creates to identify the source of the values in each observation in the output data set. This variable is a character variable whose values are the names of the variables that are transposed from the input data set. For example, in Work.Ctarget2 the values in the first observation in the output data set come from the values of the variable Year in the input data set.

The remaining transposed variables are named COL1... COLn by default. In Work.Ctarget2, the values of the variables COL1, COL2, and COL3 represent the target cargo revenue for each month in the years 1997, 1998, and 1999.

You can use PROC TRANSPOSE options to give the variables in the output data set more descriptive names. The NAME= option specifies a name for the _NAME_ variable.

The PREFIX= option specifies a prefix to use in constructing names for transposed variables in the output data set. For example, if PREFIX=Ctarget, the names of the variables are Ctarget1, Ctarget2, and Ctarget3.

proc transpose data=sasuser.ctargets
     out=work.ctarget2
     name=Month 
     prefix=Ctarget; 
run;

proc transpose data=sasuser.ctargets
     out=work.ctarget2  (rename=(col1=Ctarget1 
         col2=Ctarget2 col3=Ctarget3)) 
     name=Month;
run;

The default label for the _NAME_ variable is NAME OF FORMER VARIABLE. To see this, print the transposed data set using PROC PRINT with the LABEL option. You can use a LABEL statement to override the default label.

proc transpose data=sasuser.ctargets
     out=work.ctarget2
     name=Month
     prefix=Ctarget;
run;
proc print data=work.ctarget2 label; 
label Month=MONTH; 
run;

Remember that the transposed data set, Work.Ctarget2, needs to be merged with Sasuser.Monthsum by the values of Year and Month. Neither data set is currently structured correctly for the merge.

In order to correctly structure Work.Ctarget2 for the merge, a BY statement needs to be used with PROC TRANSPOSE. For each BY group, PROC TRANSPOSE creates one observation for each variable that it transposes. The BY variable itself is not transposed.

The following program transposes Sasuser.Ctargets by the value of Year. The resulting output data set, Work.Ctarget2, now contains 12 observations for each each year (1997, 1998, and 1999).

proc transpose data=sasuser.ctargets
     out=work.ctarget2
     name=Month
     prefix=Ctarget;
     by year; 
run;

The last step in preparing Work.Ctarget2 for the merge is to use the SORT procedure to sort the data set by Year and Month as shown in the following program:

proc sort data=work.ctarget2;
   by year month; 
run;

Notice that in the sorted version of Work.Ctarget2, the values of month are sorted alphabetically by year.

The data in Sasuser.Monthsum must also be reorganized for the merge because the month and year values in that data set are combined in the variable SaleMon.

The following program creates two new variables, Year and Month, to hold the year and month values. The values for Year are created from SaleMon using the INPUT and SUBSTR functions. The values for Month are extracted from SaleMon using the LOWCASE and SUBSTR functions.

data work.mnthsum2;
   set sasuser.monthsum(keep=SaleMon RevCargo);
   length Month $ 8;
   Year=input(substr(SaleMon,4),4.); 
   Month=substr(SaleMon,1,1) 
         ||lowcase(substr(SaleMon,2,2)); 
run;

As with Work.Ctarget2, the last step in preparing for the merge is to sort the data set by the values of Year and Month as shown in the following program:

proc sort data=work.mnthsum2;
   by year month; 
run;

Notice that in the sorted version of Work.Mnthsum2, the values of month are sorted alphabetically by year.

When the data is structured correctly, Work.Mnthsum2 and Work.Ctarget2 can be merged by the values of Year and Month as shown in the following program:

data work.merged;
   merge work.mnthsum2 work.ctarget2; 
   by year month; 
run;

PROC PRINT output shows the resulting data set Work.Merged. The values of RevCargo represent the actual cargo revenue for each month. The values of Ctarget1 represent the target cargo values for each month.

When a lookup operation depends on one or more key values, you can use the hash object. A hash object resembles a table with rows and columns and contains a key component and a data component.

The key component

Suppose you have a data set, named Sasuser.Contrib, that lists the quarterly contributions to a retirement fund. You can use the hash object to calculate the difference between the actual contribution and the goal amount.

The following program creates a hash object that stores the quarterly goal for employee contributions to the retirement fund. To calculate the difference between actual contribution and the goal amount, the program retrieves the goal amount from the hash object based on the key values.

data work.difference (drop= goalamount);
   length goalamount 8;
   if _N_ = 1 then do;
      declare hash goal( );
      goal.definekey("QtrNum");
      goal.definedata("GoalAmount");
      goal.definedone( );
      call missing(qtrnum, goalamount);
      goal.add(key:'qtr1', data:10 );
      goal.add(key:'qtr2', data:15 );
      goal.add(key:'qtr3', data: 5 );
      goal.add(key:'qtr4', data:15 );
  end;
  set sasuser.contrib;
  goal.find();
  Diff = amount - goalamount;
run;

We will see how the hash object is set up.

The hash object is a DATA step component object. Component objects are data elements that consist of attributes and methods. Attributes are the properties that specify the information that is associated with an object. An example is size. Methods define the operations that an object can perform.

To use a DATA step component object in your SAS program, you must first declare and create (instantiate) the object.

You declare a hash object using the DECLARE statement.

The following DECLARE statement creates a hash object named Goal.

data work.difference (drop= goalamount);
   length goalamount 8;
   if _N_ = 1 then do;
      declare hash goal; 

At this point, you have declared the variable Goal. It has the potential to hold a component object of the type hash.

You use the _NEW_ statement to instantiate the hash object.

The following _NEW_ statement creates an instance of the hash object and assigns it to the variable Goal.

data work.difference (drop= goalamount);
   length goalamount 8;
   if _N_ = 1 then do;
      declare hash goal;
      goal= _new_ hash(); 

As an alternative to the two-step process of using the DECLARE and _NEW_ statements to declare and instantiate a component object, you can use the DECLARE statement to declare and instantiate the component object in one step.

data Work.Difference (drop= goalamount);
   length goalamount 8;
   if _N_ = 1 then do;
      declare hash Goal(); 

Remember that the hash object uses lookup keys to store and retrieve data. The keys and the data are DATA step variables that you use to initialize the hash object by using dot notation method calls.

A key is defined by passing the key variable name to the DEFINEKEY method. Data is defined by passing the data variable name to the DEFINEDATA method. When all key and data variables are defined, the DEFINEDONE method is called. Keys and data can consist of any number of character or numeric DATA step variables.

The following code initializes the key variable QtrNum and the data variable GoalAmount.

data work.difference (drop= goalamount);
   length goalamount 8;
   if _N_ = 1 then do;
      declare hash goal();
      goal.definekey ("QtrNum"); 
      goal.definedata ("GoalAmount"); 
      goal.definedone(); 

The hash object does not assign values to key variables, and the SAS compiler cannot detect the implicit key and data variable assignments done by the hash object. Therefore, if no explicit assignment to a key or data variable appears in the program, SAS issues a note stating that the variables are uninitialized.

To avoid receiving these notes, use the CALL MISSING routine with the key and data variables as parameters. The CALL MISSING routine assigns a missing value to the specified character or numeric variables.

data Work.Difference (drop= goalamount);
length GoalAmount 8;
if _N_ = 1 then do; declare hash goal();
   goal.definekey("QtrNum");
   goal.definedata("GoalAmount");
   goal.definedone();
   call missing(qtrnum, goalamount); 

So far, you have declared and instantiated the hash object, and initialized the hash object's key and data variables. You are now ready to store data in the hash object using the ADD method. The following code uses the ADD method to load the key values qtr1, qtr2, qtr3, and qtr4 and the corresponding data values 10, 15, 5, and 15 into the hash object.

data work.difference (drop= goalamount);
length goalamount 8;
if _N_ = 1 then do; declare hash goal();
   declare hash goal( );
   goal.definekey("QtrNum");
   goal.definedata("GoalAmount");
   goal.definedone( );
   call missing(qtrnum, goalamount);
   goal.add(key:'qtr1', data:10 ); 
   goal.add(key:'qtr2', data:15 ); 
   goal.add(key:'qtr3', data: 5 ); 
   goal.add(key:'qtr4', data:15 ); 
end;

You use the FIND method to retrieve matching data from the hash object. The FIND method returns a value that indicates whether the key is in the hash object. If the key is in the hash object, then the FIND method also sets the data variable to the value of the data item so that it is available for use after the method call.

data work.difference (drop= goalamount);
   length goalamount 8;
   if _N_ = 1 then do;
      declare hash goal( );
      goal.definekey("QtrNum");
      goal.definedata("GoalAmount");
      goal.definedone( );
      call missing(qtrnum, goalamount);
      goal.add(key:'qtr1', data:10 );
      goal.add(key:'qtr2', data:15 );
      goal.add(key:'qtr3', data: 5 );
      goal.add(key:'qtr4', data:15 );
   end;
   set sasuser.contrib;
   goal.find(); 
   Diff = amount - goalamount;
run;

We will consider what happens when the program is submitted for execution.

data Work.Difference (drop= goalamount);
   length goalamount 8;
   if _N_ = 1 then do;

      declare hash goal( );
      goal.definekey("QtrNum");
      goal.definedata("GoalAmount");
      goal.definedone( );
      call missing(qtrnum, goalamount);
      goal.add(key:'qtr1', data:10 );
      goal.add(key:'qtr2', data:15 );
      goal.add(key:'qtr3', data: 5 );
      goal.add(key:'qtr4', data:15 );
   end;
   set sasuser.contrib;
   goal.find(); 
   Diff = amount - goalamount;
run;

The program executes until the DATA step encounters the end of the line. PROC PRINT output shows the completed data set.

Suppose you need to create a report that shows revenue, expenses, profits, and airport information. You have two data sets that contain portions of the required data. The SAS data set Sasuser.Revenue contains flight revenue information. The SAS data set Sasuser.Acities contains airport data including the airport code, location, and name.

To create the report, you can use a hash object to retreive matching airport data from Sasuser.Acities.

To initialize the attributes of hash variables that originate from an existing SAS data set, you can use a non-executing SET statement.

Because the IF condition is false during execution, the SET statement is compiled but not executed. The PDV is created with the variable Code, City, and Name from Sasuser.Acities.

data work.report;
   if _N_=1 then do;
      if 0 then 
         set sasuser.acities (keep=Code City Name); 

When you use this technique, the MISSING routine is not required.

The hash object that you worked with earlier in this chapter contains one key variable and one data variable. In this example, you need to associate more than one data value with each key.

In the following code, the DECLARE statement creates the Airports hash object and loads it from Sasuser.Acities. the DEFINEKEY method call defines the key to be the value of the variable Code. The DEFINEDATA method call defines the data to be the values of the variables City and Name.

data work.report;
      if 0 then
         set sasuser.acities (keep=Code City Name);
   if _N_=1 then do;
   declare hash airports (dataset: "sasuser.acities") 
   airports.definekey ("Code"); 
   airports.definedata ("City", "Name"); 
   airports.definedone();
end;

hashobject.DEFINEDATA (ALL:"YES");

You can use multiple FIND method calls to retrieve multiple data values. In the following program, the FIND method calls retrieve the value of City and Name from the Airports hash object based on the value of Origin.

data work.report;
   if _N_=1 then do;
        if 0 then set sasuser.acities (keep=Code City Name);
      declare hash airports (dataset: "sasuser.acities");
      airports.definekey ("Code");
      airports.definedata ("City", "Name");
      airports.definedone();
   end;
 set sasuser.revenue;
 airports.find(key:origin); 
 OriginCity=city;
 OriginAirport=name;
 airports.find(key:dest); 
DestCity=city;
DestAirport=name;
run;

PROC PRINT output shows the completed data set.

proc print data=work.report;
   var origin dest flightid date origincity originairport
      destcity destairport;
run;

Figure 16.4. Partial Output (first five observations of selected variables)