Defining and Referencing Two-Dimensional Arrays

A Brief Overview

Multidimensional arrays can also be created.
With a two-dimensional array, two numbers separated by a comma are used to specify the array size. Think of a two-dimensional array as consisting of rows and columns. The first number corresponds to a row, and the second number corresponds to a column. However, in the PDV, the values are still just specified as columns. A two-dimensional array is useful because you can take data from a table and load it into an array. The following figure is an example of a two-dimensional array. The array has two rows and two columns.
Figure 11.5 Two-Dimensional Array
Two-Dimensional Array

Example: Creating a Two-Dimensional Array with Initial Values

Suppose you have the Certadv.StCoup data set, which contains unique data about a store’s most recent customer order. You are asked to create a coupon value for each of the store’s customers. Customers will receive coupons ranging from 10% to 40% off their next purchase based on the type and size of their last order.
Figure 11.6 Creating a Two-Dimensional Array
Illustration of output
The ARRAY statement creates a two-dimensional array containing coupon values. The array named CpnValue has three rows and four columns. The initial values are a range from 0.10 to 0.40. Once this array is defined, you can refer to any of the columns by using array referencing.
Figure 11.7 Two-Dimensional Array with Initial Values
An illustration of a two-dimensional array with initial values and how it corresponds to the columns and rows from a data set.
However, this example references the initial value columns by using OrderType as the row and Quantity as the column. If OrderType=1 and Quantity=1, then CouponValue=.10. If OrderType=3 and Quantity=1, then CouponValue=0.20, and so on. 
data work.customercoupons;
   array cpnvalue[3,4] _temporary_ (.10, .15, .20, .25,
                                    .30, .40, .10, .15,
                                    .20, .25, .15, .10);
   set certadv.stcoup (keep=CustomerID OrderType Quantity);
   CouponValue=cpnvalue[OrderType,Quantity];
   format CouponValue percent10.;
run;
title 'Coupons for October 2019';
proc print data=work.customercoupons;
run;
Output 11.4 Partial Output of Work.CustomerCoupons
Partial Output of Work.CustomerCoupons

Example: Creating a Two-Dimensional Array to Perform Table Lookup

Suppose you are asked to combine two SAS data sets, Certadv.US_Goals and Certadv.US_Sales, and find the difference between the quarterly sales amount and the quarterly goal.
Figure 11.8 Performing Table Lookup Using an Array (Illustration)
Illustration of Performing Table Lookup Using an Array
You could use a DATA step with MERGE statement. However, that solution takes several steps because the two data sets are not laid out with a common column. You would need to use the TRANSPOSE procedure to rotate the Certadv.US_Goals data set, sort the data, and then merge the data sets. Instead, you can use a two-dimensional array in a single DATA step to accomplish the same task. The two-dimensional array solution is more efficient and requires less coding. 
data work.diffsales;
   array yrsales[2014:2018,4] _temporary_;    /*1*/
   if _N_=1 then do Yr=2014 to 2018;          /*2*/
      set certadv.us_sales;                   /*3*/
      array qtrsal[4] SalesQ1-SalesQ4;        /*4*/
      do Qtr=1 to 4;                          /*5*/
         yrsales[Yr,Qtr]=qtrsal[Qtr];
      end;
   end;
   set certadv.us_goals;                      /*6*/
   Sales=yrsales[Year,QtrNum];                /*7*/
   Difference=Sales-Goal;                     /*8*/
   drop Yr Qtr SalesQ1-SalesQ4;               /*9*/
run;
proc print data=work.diffsales;
   format Goal Sales Difference dollar14.2;
run;
1 The ARRAY statement defines a two-dimensional array named YrSales. The YrSales array contains five rows starting with 2014 and ending with 2018, and four columns that correspond to quarterly sales values. There are 20 elements in the YrSales array. Since the array is temporary, the elements do not appear in the output table.
2 The IF-THEN statement loads the YrSales array only the first time through the DATA step. The DO statement loops through enough times to read in every row of input data.
3 The SET statement reads in the four quarterly values from Certadv.US_Sales into the PDV.
4 The ARRAY statement defines a one-dimensional array named QtrSal with four elements, SalesQ1–SalesQ4.
Note: The result is two ARRAY statements. The first one contains 20 values, and the second one is used to read in the quarterly sales values from the input table.
5 The DO loop reads in the four quarterly sales values for each year, loading the YrSales array.
6 The SET statement reads in the values from Certadv.US_Goals.
7 A new variable named Sales is created and assigns the YrSales array to the variable. The Sales variable looks up the Sales value based on the values of Year and QtrNum.
8 A new variable named Difference is created. The difference between the variable Sales and Goal is calculated to determine whether the sales team met their goals each quarter for the past five years.
9 The DROP statement is used to drop the index variables and any other nonessential variable.
Output 11.5 PROC PRINT Output of Work.DiffSales
PROC PRINT Output of Work.DiffSales
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset