In the previous section, the wind chill lookup table was loaded into the WC array when the array was created. In many cases, you might prefer to load an array with values that are stored in a SAS data set rather than loading them in an ARRAY statement. Lookup values should be stored in a SAS data set when the following conditions are true:

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.

192284420

86376721

28526103

260386468

109975326

102833104

108645734

147656369

202158055

41160707

264294440

267135485

85730444

74168740

39955768

312654811

318149340

187270927

196728648

236996122

112413744

125401565

72551855

136042505

208694865

83456868

286846554

275721406

230488351

24901752

123394421

34273985

151565752

141528519

178043261

181668256

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 dimension of an array does not have to range from one to the number of elements. You can specify a range for the values of the dimension when you define the array. In this case, the dimensions of the array are specified as 3 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.

192284420

86376721

28526103

260386468

109975326

102833104

108645734

147656369

202158055

41160707

264294440

267135485

85730444

74168740

39955768

312654811

318149340

187270927

196728648

236996122

112413744

125401565

72551855

136042505

208694865

83456868

286846554

275721406

230488351

24901752

123394421

34273985

151565752

141528519

178043261

181668256

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 stores the values from Sasuser.Ctargets. The dimension of the Mnth array is 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.

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.

192284420

86376721

260386468

125401565

72551855

136042505

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;

108645734

147656369

202158055

275721406

230488351

24901752

85730444

74168740

39955768

141528519

178043261

181668256

192284420

86376721

260386468

125401565

72551855

136042505

108645734

147656369

202158055

275721406

230488351

24901752

85730444

74168740

39955768

141528519

178043261

181668256

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 from the numeric variable MonthNo. The year values are contained within the character variable SaleMon and can be extracted using the SUBSTR function. In this example, the SUBSTR function extracts 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 performs the character to numeric conversion so the value of Year is used as an array reference.

The values of Ctarget are then looked up from the Targets array based on the values 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;

192284420

86376721

260386468

125401565

72551855

136042505

108645734

147656369

202158055

275721406

230488351

24901752

85730444

74168740

39955768

141528519

178043261

181668256

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

proc print data=work.lookup1 (obs=10);
   var salemon revcargo ctarget;
run;