Fine recommended: using SAS arrays to achieve two-dimensional table query function

** Background**

In routine SAS data analysis, it is sometimes necessary to determine the size of a variable/parameter based on the values of two variables taken together, which can usually be presented in a two-dimensional table, as shown in Table 1 below. where A and B are two variables, and different combinations of values of A and B correspond to parameters (noted as Ratio). In fact, it can be easily implemented in SAS by nested IF statements, but, on closer inspection you will see that Ratio is actually a two-dimensional matrix,. For example, when A = 0.6 and B = 0.6, the Ratio takes the value 2.3, corresponding to the second column of the third row of the matrix.

Next, let's construct a simple test data set, as shown in Table 2 below, and experience the magic of SAS analysis tools together by comparing two implementations of nested IF statements and ARRAY statements to get a feel for a little trick I discovered today.

**SAS Implementation - Nested IF Statements VS ARRAY Statements**

** Nested IF Statements**

Straight to code, 25 () IF/ELSE statements, cumbersome to code and error-prone to change, next look at the ARRAY statement.

/*STEP2 nested IF statements to find two-dimensional tables*/

**DATA**NESTED_IF;

SETSAMPLE;

IFA 0.2**THENDO;**

IFB 0.4**THENRATIO =1.1;**

ELSEIFB 0.8**THENRATIO =2.1;**

ELSEIFB 1.5**THENRATIO =3.1;**

ELSEIFB 2.5**THENRATIO =4.1;**

ELSERATIO =**5.1**;

END;

ELSEIFA 0.5**THENDO;**

IFB 0.4**THENRATIO =1.2;**

ELSEIFB 0.8**THENRATIO =2.2;**

ELSEIFB 1.5**THENRATIO =3.2;**

ELSEIFB 2.5**THENRATIO =4.2;**

ELSERATIO =**5.2**;

END;

ELSEIFA 0.8**THENDO;**

IFB 0.4**THENRATIO =1.3;**

ELSEIFB 0.8**THENRATIO =2.3;**

ELSEIFB 1.5**THENRATIO =3.6;**

ELSEIFB 2.5**THENRATIO =4.3;**

ELSERATIO =**5.3**;

END;

ELSEIFA 1.2**THENDO;**

IFB 0.4**THENRATIO =1.4;**

ELSEIFB 0.8**THENRATIO =2.8;**

ELSEIFB 1.5**THENRATIO =3.4;**

ELSEIFB 2.5**THENRATIO =4.4;**

ELSERATIO =**5.4**;

END;

ELSEDO;

IFB 0.4**THENRATIO =1.5;**

ELSEIFB 0.8**THENRATIO =2.5;**

ELSEIFB 1.5**THENRATIO =3.5;**

ELSEIFB 2.5**THENRATIO =2.6;**

ELSERATIO =**5.5**;

END;

**RUN**;

** ARRAY statement**

This one is much more refreshing, with only 10 () IF statements that determine the rows and columns of the two-dimensional table they are in, plus an ARRAY statement to read this matrix in.

I won't go into the specific usage of ARRAY, but if you are interested, you can check the SAS HELP documentation directly and search for the keyword "ARRAY".

As a side note, the ARRAY statement has a "_TEMPORARY_" option inside, which means that it is reading this matrix into memory, but not outputting it. If this ARRAY statement does not include the "_TEMPORARY_" option, the result is that 25 more variables will be output, AR1 - AR25, which will greatly affect the storage space for data sets with more than 10 million records, remember!

Interested readers can download the SAS code directly at the end of the article.

/*STEP3 ARRAY statement to find a two-dimensional table*/

**DATA**TABLE_LOOKUP;

SETSAMPLE;

ARRAYAR{**5**,**5**}_TEMPORARY_

(

**1.12.13.14.15.1**

**1.22.23.24.25.2**

**1.32.33.64.35.3**

**1.42.83.44.45.4**

**1.52.53.52.65.5**

)

;

IFA 0.2**THENI =1;**

ELSEIFA 0.5**THENI =2;**

ELSEIFA 0.8**THENI =3;**

ELSEIFA 1.2**THENI =4;**

ELSEI =**5**;

IFB 0.4**THENJ =1;**

ELSEIFB 0.8**THENJ =2;**

ELSEIFB 1.5**THENJ =3;**

ELSEIFB 2.5**THENJ =4;**

ELSEJ =**5**;

RATIO = AR(I,J);

**RUN**;

Author: Manager Wang Yajun, Risk Management Department, Guangfa Bank

Big Data enthusiasts, please scan the following QR code to add "SAS Chinese Forum" volunteers as friends, "SAS Chinese Forum" volunteers will invite you into the group to communicate with the author of many articles face to face. You are also welcome to submit articles to showcase yourself and promote yourself.