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

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*/

DATANESTED_IF;

SETSAMPLE;

IFA 0.2THENDO;

IFB 0.4THENRATIO =1.1;

ELSEIFB 0.8THENRATIO =2.1;

ELSEIFB 1.5THENRATIO =3.1;

ELSEIFB 2.5THENRATIO =4.1;

ELSERATIO =5.1;

END;

ELSEIFA 0.5THENDO;

IFB 0.4THENRATIO =1.2;

ELSEIFB 0.8THENRATIO =2.2;

ELSEIFB 1.5THENRATIO =3.2;

ELSEIFB 2.5THENRATIO =4.2;

ELSERATIO =5.2;

END;

ELSEIFA 0.8THENDO;

IFB 0.4THENRATIO =1.3;

ELSEIFB 0.8THENRATIO =2.3;

ELSEIFB 1.5THENRATIO =3.6;

ELSEIFB 2.5THENRATIO =4.3;

ELSERATIO =5.3;

END;

ELSEIFA 1.2THENDO;

IFB 0.4THENRATIO =1.4;

ELSEIFB 0.8THENRATIO =2.8;

ELSEIFB 1.5THENRATIO =3.4;

ELSEIFB 2.5THENRATIO =4.4;

ELSERATIO =5.4;

END;

ELSEDO;

IFB 0.4THENRATIO =1.5;

ELSEIFB 0.8THENRATIO =2.5;

ELSEIFB 1.5THENRATIO =3.5;

ELSEIFB 2.5THENRATIO =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!

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

DATATABLE_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.2THENI =1;

ELSEIFA 0.5THENI =2;

ELSEIFA 0.8THENI =3;

ELSEIFA 1.2THENI =4;

ELSEI =5;

IFB 0.4THENJ =1;

ELSEIFB 0.8THENJ =2;

ELSEIFB 1.5THENJ =3;

ELSEIFB 2.5THENJ =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.

Recommended>>
1、113 Coin Circle Golden Horse Award Suzhou offline party summary
2、Smart healthcare is on the cusp so how should AIassisted reproduction take hold
3、BPTN Celebrity Chain Global Tour Launch in Tokyo Japan Successfully Concluded
4、TensorFlow 19 hot air just passed and version 20 is coming
5、Daimler X5 Smart Connect spy shots revealed to be genuine

已推荐到看一看 和朋友分享想法
最多200字，当前共 发送

已发送

确定
分享你的想法...
取消

确定
最多200字，当前共

发送中