1. Introduction
This module illustrates (1) how to compute
variables manually in a data step and (2) how to work across variables
using the array statement in a data step.
Consider the sample program below, which reads
in family income data for twelve months.
DATA faminc;
INPUT famid faminc1-faminc12 ;
CARDS;
1 3281 3413 3114 2500 2700 3500 3114 3319 3514 1282 2434 2818
2 4042 3084 3108 3150 3800 3100 1531 2914 3819 4124 4274 4471
3 6015 6123 6113 6100 6100 6200 6186 6132 3123 4231 6039 6215
;
RUN;PROC PRINT DATA=faminc;
RUN;
The output is shown below
F F F
F F F F F F F F F A A A
A A A A A A A A A M M M
F M M M M M M M M M I I I
A I I I I I I I I I N N N
O M N N N N N N N N N C C C
B I C C C C C C C C C 1 1 1
S D 1 2 3 4 5 6 7 8 9 0 1 2
1 1 3281 3413 3114 2500 2700 3500 3114 3319 3514 1282 2434 2818
2 2 4042 3084 3108 3150 3800 3100 1531 2914 3819 4124 4274 4471
3 3 6015 6123 6113 6100 6100 6200 6186 6132 3123 4231 6039 6215
2. Computing variables (manually)
Computing variables in a data step can be accomplished a number of ways in SAS. For example, if one
wanted to compute the amount of tax (10%) paid for each month, the simplest
way to do this is to compute 12 variables (taxinc1-taxinc12)
by multiplying each of the (faminc1-faminc12) by .10 as
illustrated below. As you see, this requires entering a command
computing the tax for each month of data (for months 1 to 12).
DATA faminc1a;
SET faminc;
taxinc1 = faminc1 * .10 ;
taxinc2 = faminc2 * .10 ;
taxinc3 = faminc3 * .10 ;
taxinc4 = faminc4 * .10 ;
taxinc5 = faminc5 * .10 ;
taxinc6 = faminc6 * .10 ;
taxinc7 = faminc7 * .10 ;
taxinc8 = faminc8 * .10 ;
taxinc9 = faminc9 * .10 ;
taxinc10= faminc10 * .10 ;
taxinc11= faminc11 * .10 ;
taxinc12= faminc12 * .10 ;
RUN;PROC PRINT DATA=faminc1a;
RUN;
The output is shown below.
F F F
F F F F F F F F F A A A T
A A A A A A A A A M M M A
F M M M M M M M M M I I I X
A I I I I I I I I I N N N I
O M N N N N N N N N N C C C N
B I C C C C C C C C C 1 1 1 C
S D 1 2 3 4 5 6 7 8 9 0 1 2 11 1 3281 3413 3114 2500 2700 3500 3114 3319 3514 1282 2434 2818 328.1
2 2 4042 3084 3108 3150 3800 3100 1531 2914 3819 4124 4274 4471 404.2
3 3 6015 6123 6113 6100 6100 6200 6186 6132 3123 4231 6039 6215 601.5 T T T
T T T T T T T T A A A
A A A A A A A A X X X
X X X X X X X X I I I
I I I I I I I I N N N
O N N N N N N N N C C C
B C C C C C C C C 1 1 1
S 2 3 4 5 6 7 8 9 0 1 2
1 341.3 311.4 250 270 350 311.4 331.9 351.4 128.2 243.4 281.8
2 308.4 310.8 315 380 310 153.1 291.4 381.9 412.4 427.4 447.1
3 612.3 611.3 610 610 620 618.6 613.2 312.3 423.1 603.9 621.5
3. Computing variables (using the array statement)
Another way to compute 12 variables representing
the amount of tax paid (10%) for each month is to use the array statement. In the example below, two "arrays" are
declared: Afaminc and Ataxinc. The elements of
Afaminc are the variables faminc1-faminc12 and the elements of
Ataxinc
are the variables taxinc1-taxinc12. You can refer to the
variables faminc1-faminc12 by referring to the elements of the array Afaminc.
For example, Afaminc(3) refers to faminc3.
Note that the array Afaminc is defined using
the existing variables faminc1-faminc12 from the dataset faminc,
whereas the values of the array Ataxinc (taxinc1-taxinc12) are
created by multiplying Afaminc (faminc1-faminc12) by .10 in the
do
loop shown below.
DATA faminc1b;
SET faminc ;ARRAY Afaminc(12) faminc1-faminc12 ;
ARRAY Ataxinc(12) taxinc1-taxinc12 ;DO month = 1 TO 12;
Ataxinc(month) = Afaminc(month) * .10 ;
END;
RUN;PROC PRINT DATA=faminc1b;
VAR faminc1-faminc12 taxinc1-taxinc12;
RUN;
The output is shown below:
f f f
f f f f f f f f f a a a t
a a a a a a a a a m m m a
m m m m m m m m m i i i x
i i i i i i i i i n n n i
O n n n n n n n n n c c c n
b c c c c c c c c c 1 1 1 c
s 1 2 3 4 5 6 7 8 9 0 1 2 1
1 3281 3413 3114 2500 2700 3500 3114 3319 3514 1282 2434 2818 328.1
2 4042 3084 3108 3150 3800 3100 1531 2914 3819 4124 4274 4471 404.2
3 6015 6123 6113 6100 6100 6200 6186 6132 3123 4231 6039 6215 601.5
t t t
t t t t t t t t a a a
a a a a a a a a x x x
x x x x x x x x i i i
i i i i i i i i n n n
O n n n n n n n n c c c
b c c c c c c c c 1 1 1
s 2 3 4 5 6 7 8 9 0 1 2
1 341.3 311.4 250 270 350 311.4 331.9 351.4 128.2 243.4 281.8
2 308.4 310.8 315 380 310 153.1 291.4 381.9 412.4 427.4 447.1
3 612.3 611.3 610 610 620 618.6 613.2 312.3 423.1 603.9 621.5
In summary, the new variables become new columns
of the dataset faminc1b and one can compute new variables as
transformations of these variables, just like any other
variables.
Note that the array statement cannot loop over observations for any one variable. If your data
are in this “long” form and you need to loop over observations, you must
reshape the data to “wide” form in order to use the array
statement. Another option for looping across observations in the “long” form is to read
the variable into a vector array using proc iml (Interactive
Matrix Language), loop over the elements of the vector, and then
append the results back to the SAS dataset using proc append.
4. Collapsing across variables (manually)
Often one needs to sum across variables (also
known as collapsing across variables). For example, let’s say the
quarterly income for each family is desired. In order to get this
information, four quarterly variables incqtr1-incqtr4 need to
be computed. Again, this can be achieved manually or by using the array
statement. Below is an example of how to compute four quarterly income variables
incqtr1-incqtr4 by simply adding together the months that
comprise a quarter.
DATA faminc2a;
SET faminc;
incqtr1 = faminc1+faminc2+faminc3 ;
incqtr2 = faminc4+faminc5+faminc6 ;
incqtr3 = faminc7+faminc8+faminc9 ;
incqtr4 = faminc10+faminc11+faminc12 ;
RUN;PROC PRINT DATA=faminc2a;
var faminc1-faminc12 incqtr1-incqtr4;
RUN;
The output is shown below.
F F F
F F F F F F F F F A A A I I I I
A A A A A A A A A M M M N N N N
M M M M M M M M M I I I C C C C
I I I I I I I I I N N N Q Q Q Q
O N N N N N N N N N C C C T T T T
B C C C C C C C C C 1 1 1 R R R R
S 1 2 3 4 5 6 7 8 9 0 1 2 1 2 3 4
1 3281 3413 3114 2500 2700 3500 3114 3319 3514 1282 2434 2818 9808 8700 9947 6534
2 4042 3084 3108 3150 3800 3100 1531 2914 3819 4124 4274 4471 10234 10050 8264 12869
3 6015 6123 6113 6100 6100 6200 6186 6132 3123 4231 6039 6215 18251 18400 15441 16485
5. Collapsing across variables (using the array statement)
This same result as above can be achieved using
the array statement. The example below illustrates how to
compute the quarterly income variables incqtr1-incqtr4 using
the array statement in a more elegant fashion. The
array Aincqtr has four elements which are computed in the do loop as the sum of sets of three months. The trick here is that the quarterly
intervals begin with months 1,4,7 and 10 respectively, which can be indexed
as (month3 – 2) where month3 is the set of numbers
{3,6,9,12}during the execution of the do loop. Hence, the first
element of the array Aincqtr is equal to the sum of the first three
elements of Afaminc, the second element of the array Aincqtr is
equal to the sum of the next three elements of Afaminc, etc.,
until the do loop is finished, as shown below.
DATA faminc2b;
SET faminc ;ARRAY Afaminc(12) faminc1-faminc12 ;
ARRAY Aincqtr(4) incqtr1-incqtr4 ;DO qtr = 1 TO 4 ;
month3 = 3*qtr;
Aincqtr(qtr) = Afaminc(month3-2) + Afaminc(month3-1) + Afaminc(month3) ;
END;
RUN;
PROC PRINT DATA=faminc2b;
var faminc1-faminc12 incqtr1-incqtr4;
RUN;
The output is shown below.
F F F
F F F F F F F F F A A A I I I I
A A A A A A A A A M M M N N N N
M M M M M M M M M I I I C C C C
I I I I I I I I I N N N Q Q Q Q
O N N N N N N N N N C C C T T T T
B C C C C C C C C C 1 1 1 R R R R
S 1 2 3 4 5 6 7 8 9 0 1 2 1 2 3 4
1 3281 3413 3114 2500 2700 3500 3114 3319 3514 1282 2434 2818 9808 8700 9947 6534
2 4042 3084 3108 3150 3800 3100 1531 2914 3819 4124 4274 4471 10234 10050 8264 12869
3 6015 6123 6113 6100 6100 6200 6186 6132 3123 4231 6039 6215 18251 18400 15441 16485
6. Identifying patterns across variables (using the array statement)
The array statement can also be used to
identify patterns across variables of a dataset. Let’s say, for example,
that one needs to know which months had income that was less than half of the income of the previous month. To obtain this information, dummy
indicators can be created to indicate in which months this occurred. In the
example below, two arrays are defined, Afaminc and Alowinc, and the elements of Afaminc and Alowinc are the variables
faminc1-faminc12 and lowinc2-lowinc12, respectively, in the SAS
dataset faminc4.
Note that only 11 dummy indicators are needed
for a 12 month period because the interest is in the change from one month to
the next. In the DO loop, when a month has income that is less
than half of the income of the previous month, the dummy indicators
lowinc2-lowinc12 get assigned a “1”. When this is not the case,
they are assigned a “0”.
Lastly, a character variable named ever
is created (with help from the array statement) indicating whether or
not there were any months where income was less than half of the income
of the previous month. This is accomplished by summing up all of the
elements of Alowinc (which contains 1’s and 0’s). If the sum of
the elements of Alowinc is greater than zero, than there was at
least one month where income was less than half of the previous month, and
ever equals “Y”. Otherwise, if there were no months where income
was less than half of the previous month, the sum of the elements of
Alowinc is zero, and ever equals
“N”.
DATA faminc4;
SET faminc ;ARRAY Afaminc(12) faminc1-faminc12 ;
ARRAY Alowinc(2:12) lowinc2-lowinc12 ;DO month = 2 to 12 ;
IF Afaminc(month) < ( Afaminc(month-1) / 2) THEN Alowinc(month) = 1;
ELSE Alowinc(month) = 0;
END;sum_low=0; /*THIS INITIALIZES sum_low TO ZERO AT THE BEGINNING OF THE LOOP*/;
DO month = 2 to 12 ;
sum_low =sum_low + Alowinc(month) ;
END;IF sum_low GT 0 THEN ever=’Y’;
IF sum_low EQ 0 THEN ever=’N’;
RUN;PROC PRINT DATA=faminc4;
VAR famid faminc1-faminc12 lowinc2-lowinc12 ever;
RUN;
The output is shown below.
F F F L L L
F F F F F F F F F A A A L L L L L L L L O O O
A A A A A A A A A M M M O O O O O O O O W W W
F M M M M M M M M M I I I W W W W W W W W I I I
A I I I I I I I I I N N N I I I I I I I I N N N E
O M N N N N N N N N N C C C N N N N N N N N C C C V
B I C C C C C C C C C 1 1 1 C C C C C C C C 1 1 1 E
S D 1 2 3 4 5 6 7 8 9 0 1 2 2 3 4 5 6 7 8 9 0 1 2 R
1 1 3281 3413 3114 2500 2700 3500 3114 3319 3514 1282 2434 2818 0 0 0 0 0 0 0 0 1 0 0 Y
2 2 4042 3084 3108 3150 3800 3100 1531 2914 3819 4124 4274 4471 0 0 0 0 0 1 0 0 0 0 0 Y
3 3 6015 6123 6113 6100 6100 6200 6186 6132 3123 4231 6039 6215 0 0 0 0 0 0 0 0 0 0 0 N