0% found this document useful (0 votes)

2 views148 pages

Array PDF

The document discusses array processing in SAS Data Steps, focusing on table lookup techniques and the use of one-dimensional and multidimensional arrays. It explains how to define arrays, perform repetitive calculations, and utilize arrays for data lookups using various methods such as IF-THEN/ELSE statements, DATA step merges, PROC SQL joins, and user-defined formats. Additionally, it highlights the benefits of using arrays to simplify code and improve efficiency in data manipulation.

Uploaded by

ss31saha

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

2 views148 pages

Array PDF

Uploaded by

ss31saha

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

Array processing in Data Step

Copyright © 2010, SAS Institute Inc. All rights reserved.

1
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Array processing in Data Step

1 Introduction to Table Lookup Techniques

2 One-Dimensional Arrays

3 Multidimensional Arrays

4 Loading a Multidimensional Array from a SAS Data Set

2
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Array processing in Data Step

1 Introduction to Table Lookup Techniques

2 One-Dimensional Arrays

3 Multidimensional Arrays

4 Loading a Multidimensional Array from a SAS Data Set

3
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Objectives
◼ Define table lookup.
◼ List table lookup techniques.

4
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Introduction
There is often a need to combine or look up data from
multiple sources to create meaningful reports.

With the volume of data that exists today, you might often need to combine or
look up data from multiple sources to create meaningful reports.

5
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Introduction
When data sources do not share a common structure,
a lookup table can match them.

6
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Lookup Table Data

You can use several techniques to look up data.

Continent Continent
ID Name
91 North America

93 Europe

94 Africa

95 Asia

96 Australia/Pacific

7
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

IF-THEN/ELSE Statements
Lookup tables can be SAS programming statements.

data countryinfo;
set [Link];
if ContinentID=91
then Continent='North America';
else if ContinentID=93
then Continent='Europe';
else if ContinentID=94
then Continent='Africa';
else if ContinentID=95
then Continent='Asia';
else if ContinentID=96
then Continent='Australia/Pacific';
run;

8
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

DATA Step Merge

Lookup tables can be SAS data sets that are accessed
during a DATA step merge.
data countryinfo;
merge [Link]
[Link];
by ContinentID;
run;

9
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

PROC SQL Join

Lookup tables can be SAS data sets that are accessed
during a PROC SQL join.
proc sql;
create table countryinfo as
select * from
[Link] a, [Link] b
where [Link]=[Link];
quit;

10
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

User-Defined Formats
Lookup tables can be user-defined formats that are
accessed with a FORMAT statement or PUT function.
proc format;
value ContName
91='North America' 93='Europe'
94='Africa' 95='Asia'
96='Australia/Pacific';
run;
proc print data=[Link];
format ContinentID ContName.;
run;
data countryinfo;
set [Link];
Continent=put(ContinentID,ContName.);
run;
11

11
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Arrays
Lookup tables can be arrays.

data countryinfo;
array ContName{91:96} $ 30 _temporary_
('North America',
' ',
'Europe',
'Africa',
'Asia',
'Australia/Pacific');
set [Link];
Continent=ContName{ContinentID};
run;

p1
12

12
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

13
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Array processing in Data Step

1 Introduction to Table Lookup Techniques

2 One-Dimensional Arrays

3 Multidimensional Arrays

4 Loading a Multidimensional Array from a SAS Data Set

14
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Objectives
◼ Explain the concepts of SAS arrays.
◼ Use SAS arrays to perform repetitive calculations.
◼ Use arrays as arguments to SAS functions.
◼ Explain array functions.
◼ Use arrays to create new variables.
◼ Use arrays to perform a table lookup

15
15

15
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Array Processing
You can use arrays to simplify programs that do the
following:
◼ perform repetitive calculations
◼ create many variables with the same attributes

◼ read data
◼ compare variables

◼ perform a table lookup

16
16

16
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Business Scenario
The orion.employee_donations data set contains
quarterly contribution data for each employee. Orion
management is considering a 25% matching program.
Calculate each employee’s quarterly contribution, including
the proposed company supplement.

Partial Listing of orion.employee_donations

Employee_ID Qtr1 Qtr2 Qtr3 Qtr4
120265 . . . 25
120267 15 15 15 15
120269 20 20 20 20
120270 20 10 5 .
120271 20 20 20 20
120272 10 10 10 10

17
17

This business scenario uses the orion.employee_donations data set. It has 4

numeric variables that hold the quarterly donation amounts. (There are 2
additional variables that will not be used in this example: Paid_By and
Recipients.

17
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Performing Repetitive Calculations

data charity;
set orion.employee_donations;
keep employee_id qtr1-qtr4;
Qtr1=Qtr1*1.25;
Qtr2=Qtr2*1.25;
Qtr3=Qtr3*1.25;
Qtr4=Qtr4*1.25;
run;
proc print data=charity noobs;
run;
Partial PROC PRINT Output
Employee_ID Qtr1 Qtr2 Qtr3 Qtr4

120265 . . . 31.25
120267 18.75 18.75 18.75 18.75
120269 25.00 25.00 25.00 25.00
120270 25.00 12.50 6.25 .
p2
18
18

Looking at this you may think – let’s use a DO loop so that we can replace the
4 calculations with a single calculation, but…
Note: The KEEP statement results in droppping the two unwanted variables,
Paid_By and Recipients.

18
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Performing Repetitive Calculations

The four calculations cannot be replaced by a single
calculation inside a DO loop because they are not
identical.
data charity;
set orion.employee_donations;
keep employee_id qtr1-qtr4;
Qtr1=Qtr1*1.25;
Qtr2=Qtr2*1.25; do i=1 to 4;
Qtr3=Qtr3*1.25; ?
Qtr4=Qtr4*1.25; end;
run;
proc print data=charity noobs;
run;
A SAS array can be used to simplify this code.

19
19

They cannot be replaced because each one refers to a different variable.

Think about it – what statement can be used in the DO loop to perform the
equivalent calculations?? There isn’t one – unless we use an array.

19
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Use Arrays to Simplify Repetitive Calculations

An array provides an alternate way to access values
in the PDV, which simplifies repetitive calculations.
data charity;
set orion.employee_donations;
keep employee_id qtr1-qtr4;
Qtr1=Qtr1*1.25;
Qtr2=Qtr2*1.25;
Qtr3=Qtr3*1.25; An array can be used
Qtr4=Qtr4*1.25; to access Qtr1-Qtr4.
run;
proc print data=charity noobs;
run;
PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4
ID

20
20

The array is an alternate way of accessing values stored in the PDV.

20
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

What Is a SAS Array?

A SAS array
◼ is a temporary grouping of SAS variables that are
arranged in a particular order
◼ is identified by an array name
◼ must contain all numeric or all character variables

◼ exists only for the duration of the current DATA step

◼ is not a variable.

21
21

In SAS, an array is simply a way to refer to a group of variables with a single

name.

21
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Why Use a SAS Array?

Create an array named Contrib and use it to access
the four numeric variables, Qtr1 through Qtr4.

array name CONTRIB

PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4
ID

22
22

SAS arrays are different from arrays in many other programming languages. In
SAS, an array is not a data structure. It is simply a convenient way of
temporarily identifying a group of variables.
We’ll define an array named CONTRIB and use it to refer to the 4 variables,
Qtr1-Qtr4 using a common name. This will allow us to use a DO loop to
access the array elements.

22
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Array Elements
Each value in an array is called an element.

array name CONTRIB

PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4
ID

First Second Third Fourth

element element element element

23
23

The variables are referred to as elements of the array.

23
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Referencing Array Elements

Each element is identified by a subscript that represents
its position in the array. When you use an array reference,
the corresponding value is substituted for the reference.

array name CONTRIB

PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4
ID

Contrib{1} Contrib{2} Contrib{3} Contrib{4}

array references

24
24

And each element is accessed using the array name and a subscript. Contrib-
sub-1 is an alternate way of referring to Qtr1. Any time you use Contrib{1}
the value of Qtr1 will be used. The same is true for Contrib{2} thru
Contrib{4}.

24
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Defining an Array
The ARRAY statement is a compile-time statement
that defines the elements in an array.
ARRAY array-name {subscript} <$> <length>
<array-elements>;

array Contrib{4} Qtr1 Qtr2 Qtr3 Qtr4;

array name CONTRIB

PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4
ID

Contrib{1} Contrib{2} Contrib{3} Contrib{4}

25
25

array-name specifies the name of the array.

{subscript} describes the number and arrangement of elements in the array by
using an asterisk, a number, numbers separated by commas (for multi-
dimensional arrays), a range of numbers, or an expression. The subscriptis
enclosed in braces ({}). Brackets ([]) and parentheses (()) are also allowed.
$ indicates that the elements in the array are character elements. The dollar
sign is not necessary if the elements in the array were previously defined as
character elements.
length specifies the length of elements in the array that were not previously
assigned a length.
array-elements names the elements that make up the array. Array elements can
be listed in any order.
Note:
• The elements are created if they do not already exist in the PDV.
• Array references cannot be used in compile-time statements such as
LABEL, FORMAT, DROP, KEEP, or LENGTH statements.
• If you use a function name as the name of the array, SAS treats
parenthetical references that involve the name as array references, not
function references, for the duration of the DATA step.

25
The array Contrib has 4 elements. The four variables, Qtr1, Qtr2, Qtr3, and Qtr4, can
now be referenced via the array name Contrib, with an appropriate subscript.

25
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Defining an Array
An alternate syntax uses an asterisk instead of a subscript.
SAS determines the subscript by counting the variables in
the element list. The element list must be included.

array Contrib{*} Qtr1 Qtr2 Qtr3 Qtr4;

Subscript is 4. Element list

The alternate syntax is often used when the array elements

are defined with a SAS variable list.

array Contrib{*} Qtr:;

26
26

An asterisk can be used instead of a subscript in the array definition to let SAS
determine the number of elements in the array. This is convenient when using
a variable list.
You can use special SAS name lists to reference variables that were previously
defined in the same DATA step. The _CHARACTER_ variable lists character
values only. The _NUMERIC_ variable lists numeric values only.
Avoid using the _ALL_ special SAS name list to reference variables, because
the elements in an array must be either all character or all numeric values.

26
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Defining an Array
Variables that are elements of an array do not need
the following:
◼ to have similar, related, or numbered names
◼ to be stored sequentially
◼ to be adjacent

array Amt{*} Q1 Q2 ThrdQ Qtr4;

AMT

PDV
ID Q1 Total ThrdQ Q2 Qtr4

Amt{1} Amt{3} Amt{2} Amt{4}

27
27

The elements need not be adjacent nor have common variable names.

27
C o p yr i g h t © 2 0 1 4 , S AS In s t i t u t e In c . Al l r i g h t s r e s e r ve d .

Specifying Lower and Upper Bounds

upper lower upper

bound bound bound

array Celsius[3]; array Celsius[1:3];

implicitly specifies a bound explicitly specifies a bound

range of 1 to n, where n is range of the lower bound to
the number of elements the upper bound

If when you define the array, you only specify one number for your one dimension, you
are specifying an upper bound. This is an implicit method which assumes a lower bound
of 1 to an upper bound equal to the number of elements. In this example, 1 to 3. An
alternative syntax for writing this one dimension, is to explicitly specify a lower bound
followed by a colon and then an upper bound. A value of 1 colon 3 is a lower bound
starting at 1 and going to the upper bound of 3. When specifying bounds, you must
specify values from a low value to a high value.
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Short Answer Poll

Open and submit [Link]. What is the cause of the error?

data charity(keep=Employee_ID Qtr1-Qtr4);

set orion.employee_donations;
array Contrib1{3} Qtr1-Qtr4;
array Contrib2{5} qtr:;
/* additional SAS statements */
run;

p3
29

The subscript and the number of elements in the list do not agree.

29
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Short Answer Poll – Correct Answer

Open and submit [Link]. View the log to determine
the cause of the error. The subscript and the number
of elements in the list do not agree.
data charity(keep=Employee_ID Qtr1-Qtr4);
set orion.employee_donations;
array Contrib1{3} Qtr1-Qtr4;
array Contrib2{5} qtr:;
/* additional SAS statements */
run;
The subscript and
Partial SAS Log element list must agree.
177 array Contrib1{3} Qtr1-Qtr4;
ERROR: Too many variables defined for the dimension(s) specified
for the array Contrib1.
178 array Contrib2{5} qtr:;
ERROR: Too few variables defined for the dimension(s) specified
for the array Contrib2.
30

Type answer here

30
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Using a DO Loop to Process an Array

data charity;
set orion.employee_donations;
keep Employee_ID Qtr1-Qtr4;
array Contrib{4} Qtr1-Qtr4;
do i=1 to 4;
Contrib{i}=Contrib{i}*1.25;
end;
run;
DO index-variable=1 TO number-of-elements-in-array;
<additional SAS statements>
END;

To reference an element, the index variable is often used

as a subscript: array-name{index-variable}

p4
31
31

A DO loop is a convenient way to process every element in an array. To

process particular elements of an array, specify those elements as the range of
the iterative DO statement.
By default, SAS includes index-variablein the output data set. Use a DROP or
KEEP statement or the DROP= or KEEP= data set option to prevent the index
variable from being written to your output data set.

The DIM( ) function can be used to determine how many elements are in an
arry. It is covered in the next section.

The index variable, i, is not written to the output data set because it is not
listed in the KEEP statement.

Let’s go back to our Business Scenario – remember we want to increase each

quarterly donation with a 25% company contribution. Now we can do this in
an iterative DO loop since the array gives us a common name that can be used
to refer to each of the four values.

31
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

First Iteration of the DO Loop

data charity;
set orion.employee_donations;
keep Employee_ID Qtr1-Qtr4;
array Contrib{4} Qtr1-Qtr4;
do i=1 to 4;
Contrib{i}=Contrib{i}*1.25;
end;
run;

when i=1

Contrib{1}=Contrib{1}*1.25;

Qtr1=Qtr1*1.25;

32
32 ...

32
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Second Iteration of the DO Loop

data charity;
set orion.employee_donations;
keep Employee_ID Qtr1-Qtr4;
array Contrib{4} Qtr1-Qtr4;
do i=1 to 4;
Contrib{i}=Contrib{i}*1.25;
end;
run;

when i=2

Contrib{2}=Contrib{2}*1.25;

Qtr2=Qtr2*1.25;

33
33 ...

33
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Third Iteration of the DO Loop

data charity;
set orion.employee_donations;
keep Employee_ID Qtr1-Qtr4;
array Contrib{4} Qtr1-Qtr4;
do i=1 to 4;
Contrib{i}=Contrib{i}*1.25;
end;
run;

when i=3

Contrib{3}=Contrib{3}*1.25;

Qtr3=Qtr3*1.25;

34
34 ...

34
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Fourth Iteration of the DO Loop

data charity;
set orion.employee_donations;
keep Employee_ID Qtr1-Qtr4;
array Contrib{4} Qtr1-Qtr4;
do i=1 to 4;
Contrib{i}=Contrib{i}*1.25;
end;
run;

when i=4

Contrib{4}=Contrib{4}*1.25;

Qtr4=Qtr4*1.25;

35
35

35
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Output: Using a Do Loop to Process an Array

proc print data=charity noobs;
run;

Partial PROC PRINT Output

Employee_ID Qtr1 Qtr2 Qtr3 Qtr4

120265 . . . 31.25
120267 18.75 18.75 18.75 18.75
120269 25.00 25.00 25.00 25.00
120270 25.00 12.50 6.25 .
120271 25.00 25.00 25.00 25.00
120272 12.50 12.50 12.50 12.50
120275 18.75 18.75 18.75 18.75
120660 31.25 31.25 31.25 31.25
120662 12.50 . 6.25 6.25

36
36

36
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

37
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Using an Array as a Function Argument

The program below passes an array to the SUM function.
data test;
set orion.employee_donations;
array val{4} Qtr1-Qtr4;
Tot1=sum(of Qtr1-Qtr4);
Tot2=sum(of val{*});
The array is passed as if
run;
proc print data=test; it were a variable list.
var Employee_ID Tot1 Tot2;
run;

Partial PROC PRINT Output

Obs Employee_ID Tot1 Tot2

1 120265 25 25
2 120267 60 60
3 120269 80 80 p5
38
38

You cannot refer to an entire array just by its name, but you can use
arrayName{*}. In this example the entire array is passed to the SUM function
as a variable list.

38
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

DIM Function
The DIM function returns the number of elements
in an array. This value is often used as the stop value
in a DO loop.
data charity;
set orion.employee_donations;
keep Employee_ID Qtr1-Qtr4;
array Contrib{*} qtr:;
do i=1 to dim(Contrib);
Contrib{i}=Contrib{i}*1.25;
end;
run;
DIM(array_name)

P6
39
39

The DIM function returns the number of elements in an array. Here it is used
to determine the stop value for the loop.

The HBOUND function returns the upper bound of an array. The LBOUND
function returns the lower bound of an array. The following DO loop processes
every element in an array:
do i=lbound(items) to hbound(items);
*process items{i};
end;
In most arrays, subscripts range from 1 to n, where nis the number of elements
in the array. The value 1 is a convenient lower bound. Thus, you do not need to
specify the lower bound. However, specifying both bounds is useful when the
array dimensions have a beginning point other than 1. For example, the
following ARRAY statement defines an array of 10 elements, with subscripts
that range from 5 to 14:
array items{5:14} n5-n14;

39
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Using an Array to Create Numeric Variables

An ARRAY statement can be used to create new
variables in the program data vector.
array Pct{4} Pct1-Pct4;

If Pct1 through Pct4 do not exist in the PDV, they are

created.

This statement produces the same results:

array Pct{4};
PDV
Pct1 Pct2 Pct3 Pct4
N8 N8 N8 N8

40
40

Creating multiple numeric variables.

40
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Using an Array to Create Character Variables

Define an array named Month to create six variables
to hold character values with a length of 10.

array Month{6} $ 10;

PDV
Month1 Month2 Month3 Month4 Month5 Month6
$ 10 $ 10 $ 10 $ 10 $ 10 $ 10

41
41

Creating multiple character variables. They are all the same length.

41
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Business Scenario
Using orion.employee_donations as input, calculate
the percentage that each quarterly contribution
represents of the employee’s total annual contribution.
Create four new variables to hold the percentages.
Employee_ID Qtr1 Qtr2 Qtr3 Qtr4

120265 . . . 25
120267 15 15 15 15

Employee_ID Percent1 Percent2 Percent3 Percent4

120265 . . . 100%
%
120267 25% 25% 25% 25%

42
42

This program requires the creation of 4 numeric variables.

42
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Creating Variables with Arrays

data percent(drop=i);
set orion.employee_donations;
array Contrib{4} Qtr1-Qtr4;
array Percent{4};
Total=sum(of contrib{*});
do i=1 to 4;
percent{i}=contrib{i}/total;
end;
run;

The second ARRAY statement creates four numeric

variables: Percent1, Percent2, Percent3, and Percent4.

p7
43
43

Use an array to create 4 numeric variables.

43
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Output: Creating Variables with Arrays

proc print data=percent noobs;
var Employee_ID Percent1-Percent4;
format Percent1-Percent4 percent6.;
run;
Partial PROC PRINT Output (124 Total Observations)
Employee_ID Percent1 Percent2 Percent3 Percent4

120265 . . . 100%
120267 25% 25% 25% 25%
120269 25% 25% 25% 25%
120270 57% 29% 14% .
120271 25% 25% 25% 25%
120272 25% 25% 25% 25%
120275 25% 25% 25% 25%
120660 25% 25% 25% 25%
120662 50% . 25% 25%
120663 . . 100% .
120668 25% 25% 25% 25%

44
44

Display the ID and the 4 new variables. Use a Percent6. format for the new
variables. The PERCENTw.d format multiplies values by 100, formats them in
the same way as the BESTw.d format and adds a percent sign (%) to the end of
the formatted value. Negative values are enclosed in parentheses. The
PERCENTw.d format provides room for a percent sign and parentheses, even
if the value is not negative.

44
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Business Scenario
Using orion.employee_donations as input, calculate
the difference in each employee’s contribution from one
quarter to the next.
Employee_ID Qtr1 Qtr2 Qtr3 Qtr4

120265 . . . 25
120267 15 15 15 15
120269 20 20 20 20
120270 20 10 5 .
Diff1 = Qtr2 – Qtr1
Diff2 = Qtr3 – Qtr2
Diff3 = Qtr4 – Qtr3
Employee_ID Diff1 Diff2 Diff3

120265 . . .
120267 0 0 0
120269 0 0 0
120270 -10 -5 .

45
45

Calculate 3 differences and store them in three new variables.

45
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Creating Variables with Arrays

data change;
set orion.employee_donations;
drop i;
array Contrib{4} Qtr1-Qtr4;
array Diff{3};
do i=1 to 3;
Diff{i}=Contrib{i+1}-Contrib{i};
end;
run;

The Contrib array refers to existing variables. The Diff

array creates three variables: Diff1, Diff2, and Diff3.

p8
46
46

Notice that an expression can be used as a subscript – provided it evaluates to

a numeric value in the range of the array elements.

46
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Creating Variables with Arrays

data change;
set orion.employee_donations;
drop i;
array Contrib{4} Qtr1-Qtr4;
array Diff{3};
do i=1 to 3;
Diff{i}=Contrib{i+1}-Contrib{i};
end;
run;

when i=1

Diff{1}=Contrib{2}-Contrib{1};

Diff1=Qtr2-Qtr1;
47
47 ...

47
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Creating Variables with Arrays

data change;
set orion.employee_donations;
drop i;
array Contrib{4} Qtr1-Qtr4;
array Diff{3};
do i=1 to 3;
Diff{i}=Contrib{i+1}-Contrib{i};
end;
run;

when i=2

Diff{2}=Contrib{3}-Contrib{2};

Diff2=Qtr3-Qtr2;
48
48 ...

48
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Creating Variables with Arrays

data change;
set orion.employee_donations;
drop i;
array Contrib{4} Qtr1-Qtr4;
array Diff{3};
do i=1 to 3;
Diff{i}=Contrib{i+1}-Contrib{i};
end;
run;

when i=3

Diff{3}=Contrib{4}-Contrib{3};

Diff3=Qtr4-Qtr3;
49
49

49
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Creating Variables with Arrays

proc print data=change noobs;
var Employee_ID Diff1-Diff3;
run;

Partial PROC PRINT Output (124 Total Observations)

Employee_ID Diff1 Diff2 Diff3

120265 . . .
120267 0 0 0
120269 0 0 0
120270 -10 -5 .
120271 0 0 0
120272 0 0 0
120275 0 0 0
120660 0 0 0
120662 . . 0

50
50

There are missing values because there were missing values in the intpu data
set. We’ll see later how we can use the SUM function to ignore missing values
when calculating the diffrence of two values.

50
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

51
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Business Scenario
Determine the difference between employee contributions
and the quarterly goals of $10, $20, $20, and $15. Use a
lookup table to store the quarterly goals.

Employee_ID Qtr1 Qtr2 Qtr3 Qtr4

120265 . . . 25
120267 15 15 15 15 Diff1 = Qtr1 – 10
120269 20 20 20 20 Diff2 = Qtr2 – 20
Diff3 = Qtr3 – 20
Diff4 = Qtr4 – 15
Employee_ID Diff1 Diff2 Diff3 Diff4

120265 . . . 10
120267 5 -5 -5 0
120269 10 0 0 5

52
52

Compare each quarterly value with the goal set by management.

Use 3 arrays: one to access Qtr1-Qtr4
one to create the 4 differences
one to use as a lookup table.

52
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Assigning Initial Values to an Array

When the initial value list is specified, all elements
behave as if they were named in a RETAIN statement.
This is often used to create a lookup table, that is, a list of
values to refer to during DATA step processing.

ARRAY array-name {subscript} <$> <length>

<array-elements> <(initial-value-list)>;

array Target{5} (50,100,125,150,200);

PDV
Target1 Target2 Target3 Target4 Target5
R R R R R
N8 N8 N8 N8 N8
50 100 125 150 200
53
53

(initial-value-list) lists the initial values for the corresponding array elements.
The values for elements can be numbers or character strings. Character strings
must be enclosed in quotation marks. Use commas or spaces to separate values
in the list.

When an initial value list is provided for array elements, the values are
retained. The variables are not read-only – so you can change them, but if you
do not change them, they will have constant values for the life of the DATA
step.

Elements and values are matched by position. If there are more array elements
than initial values, the remaining array elements are assigned missing values
and SAS issues a warning.

53
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation: What Variables Are Created?

data compare(drop=i Goal1-Goal4);
set orion.employee_donations;
array Contrib{4} Qtr1-Qtr4;
array Diff{4};
array Goal{4} (10,20,20,15);
do i=1 to 4;
Diff{i}=Contrib{i}-Goal{i};
end;
run;

Partial PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4
ID

p9
54
54

54
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation: What Variables Are Created?

data compare(drop=i Goal1-Goal4);
set orion.employee_donations;
array Contrib{4} Qtr1-Qtr4;
array Diff{4}; No variables
array Goal{4} (10,20,20,15); created
do i=1 to 4;
Diff{i}=Contrib{i}-Goal{i};
end;
run;

Partial PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4
ID

55
55 ...

55
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation: What Variables Are Created?

data compare(drop=i Goal1-Goal4);
set orion.employee_donations;
array Contrib{4} Qtr1-Qtr4;
array Diff{4};
array Goal{4} (10,20,20,15);
do i=1 to 4;
Diff{i}=Contrib{i}-Goal{i};
end;
run;
Partial PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4 Diff1
ID

Diff2 Diff3 Diff4

56
56 ...

56
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation: What Variables Are Created?

Diff2 Diff3 Diff4 Goal1 Goal2 Goal3 Goal4

57
57 ...

57
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation: What Variables Are Created?

Diff2 Diff3 Diff4 Goal1 Goal2 Goal3 Goal4 i

58
58 ...

58
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation: Drop Flags Are Set

Diff2 Diff3 Diff4 Goal1 Goal2 Goal3 Goal4 i

59
59 ...

Drop flags are set. Notice that variables in the lookup table are dropped.

59
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation: Retain Flags Are Set

R R R R
Diff2 Diff3 Diff4 Goal1 Goal2 Goal3 Goal4 i

60
60 ...

Retain flags are set on the elements of the lookup table.

60
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

PDV Is Initialized
data compare(drop=i Goal1-Goal4);
set orion.employee_donations;
array Contrib{4} Qtr1-Qtr4; Initialize PDV
array Diff{4};
array Goal{4} (10,20,20,15);
do i=1 to 4;
Diff{i}=Contrib{i}-Goal{i};
end;
run;
Partial PDV
Employee_
Qtr1 Qtr2 Qtr3 Qtr4 Diff1
ID
. . . . . .
R R R R
Diff2 Diff3 Diff4 Goal1 Goal2 Goal3 Goal4 i
. . . 10 20 20 15 .

61
61

Initial values are set when the PDV is initialized.

61
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Creating a Temporary Lookup Table

You can use the keyword _TEMPORARY_ in an ARRAY
statement to indicate that the elements are not needed
in the output data set.
data compare(drop=i);
set orion.employee_donations;
array Contrib{4} Qtr1-Qtr4;
array Diff{4};
array Goal{4} _temporary_ (10,20,20,15);
do i=1 to 4;
Diff{i}=Contrib{i}-Goal{i};
end;
run;

p10
62
62

You can make the lookup table temporary by using the _temporary_ keyword.
Remove the DROP= option for the table.

Temporary tables are not stored in the PDV – they are stored in memory. The
elements are not named, so you cannot refer to them as Goal1-Goal4. You
must refer to them as Goal{1} through Goal{4}.

Arrays of temporary elements are useful when the only purpose for creating an
array is to perform a calculation. To preserve the result of the calculation,
assign it to a variable.

Note: For a character array, the type and length of elements must be specified
before the _temporary_ keyword.

62
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Output: Creating a Temporary Lookup Table

proc print data=compare noobs;
var employee_id diff1-diff4;
run;

Partial PROC PRINT Output (124 Total Observations)

Employee_ID Diff1 Diff2 Diff3 Diff4

120265 . . . 10
120267 5 -5 -5 0
120269 10 0 0 5
120270 10 -10 -15 .
120271 10 0 0 5

What can be done to ignore missing values?

63
63

If the quarterly donation was missing, the Difference is missing because the
values are being subtracted.

Like addition, in subtraction if any operands have missing values, the result is
a missing value.

63
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

The SUM Function Ignores Missing Values

The SUM function ignores missing values. It can be
used to calculate the difference between the quarterly
contribution and the corresponding goal.

data compare(drop=i);
set orion.employee_donations;
array Contrib{4} Qtr1-Qtr4;
array Diff{4};
array Goal{4} _temporary_ (10,20,20,15);
do i=1 to 4;
Diff{i}=sum(Contrib{i},-Goal{i});
end;
run;

p11
64
64

Use the SUM function with a negative prefix (or unary minus) on the operand
to be subtracted. Now the missing values are ignored.

64
C o p yr i g ht © 2 0 1 4 , S A S Ins ti tut e Inc . A ll r i g hts r e s e r ve d .

Output: Lookup Table Application

proc print data=compare noobs;
var employee_id diff1-diff4;
run;

Partial PROC PRINT Output (124 Total Observations)

Employee_ID Diff1 Diff2 Diff3 Diff4

120265 -10 -20 -20 10

120267 5 -5 -5 0
120269 10 0 0 5
120270 10 -10 -15 -15
120271 10 0 0 5

The missing values were handled as if no contribution

were made for that quarter.

65
65

65
Rotating Data: using arrays

66
Co p y r ig h t © S A S I n s titu te I n c . A ll r ig h ts r es er v ed .

Another reason to use arrays is to rotate data. For this example, we have a table
containing five rows which corresponds to five years and four columns of precipitation
in inches corresponding to four quarters. However, we need the quarter precipitation
values to be in one column so that we can create a bar chart showing average
precipitation by quarter. In the end, we need a table with 20 rows, 4 quarters per each of
the 5 years to feed into our graphing procedure. Notice in the bar chart, the labels at the
end of each bar. The average precipitation for quarter 1 is 7.65 inches, quarter 2 is 6.26
inches, quarter 3 is 7.56 inches, and quarter 4 is 9.12 inches. Remember those values
because we will use them later in another scenario.
Rotating Data: using arrays

array Q[4] qtr1-qtr4;

do Quarter=1 to 4;

Amount=Q[Quarter];

output;

end;

67 p12
Co p y r ig h t © S A S I n s titu te I n c . A ll r ig h ts r es er v ed .

In order to rotate the data, we can have an array pointing to the four quarter values.
Then we will go through a DO loop four times, each time assigning the appropriate
quarter value to the Precip column. We can’t forget to use the OUTPUT statement so
that each Precip value will be output as its own row.
You might be thinking “Couldn’t I just use PROC TRANSPOSE to rotate this data”
Sure, but the power of the DATA step is that we can rotate and manipulate data at the
same time.
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

68
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Business Scenario
Calculate the difference between the current salary of
each employee hired in a specific year and the average
salary of all employees hired that year.
year of hire=????

salary
difference

You need to perform this same step for all employees, regardless of their year
of hire. First, you need a table of average salaries for each year of hire.

69
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Using a Table Lookup

[Link] contains the salary statistics for all
Orion Star employees who were hired in the years 1978
through 2011, including average salaries.
Partial [Link]
Statistic Yr1978 Yr1979 Yr1980 …

MedianSalary 30025 29442.5 30020 …

StdSalary 28551.9 9918.35 22356.91 …

SumSalary 2393860 132150 235030 …

AvgSalary 39243.61 33037.5 39171.67 …

70
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Using a Table Lookup

[Link] contains each Orion Star
employee’s hire date and current salary.

Partial [Link]
EmployeeID Salary BirthDate EmployeeHireDate

120101 163040 18AUG1980 01JUL2007

120102 108255 11AUG1973 01JUN1993

120103 87975 22JAN1953 01JAN1978

120104 46230 11MAY1958 01JAN1985

120105 27110 21DEC1978 01MAY2003

71
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Multiple Choice Poll

Which data set can be used to populate the array?
a. [Link]
b. [Link]

72
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Multiple Choice Poll – Correct Answer

Which data set can be used to populate the array?
a. [Link]
b. [Link]

[Link] contains the average salaries that

need to be looked up.

73
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Using a Table Lookup

Combine the data with a table lookup.
Partial [Link]
Statistic Yr1978 Yr1979 Yr1980

MedianSalary 30025 29442.5 30020

StdSalary 28551.9 9918.35 22356.91

SumSalary 2393860 132150 235030

AvgSalary 39243.61 33037.5 39171.67

Partial [Link] YEAR(SAS-date-value)

EmployeeID Salary BirthDate EmployeeHireDate

120101 163040 18AUG1980 01JUL2007

120102 108255 11AUG1973 01JUN1993

120103 87975 22JAN1953 01JAN1978

120104 46230 11MAY1958 01JAN1985

120105 27110 21DEC1978 01MAY2003

To calculate the salary differences, you can look up the average salaries from a
table. The key to the lookup is the hire year.

74
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Using a One-Dimensional Array

data compare;
keep EmployeeID YearHired Salary
Average SalaryDif;
format Salary Average SalaryDif dollar12.2;
array yr{1978:2011} Yr1978-Yr2011;
if _n_=1 then set [Link]
(where=(Statistic='AvgSalary'));
set [Link]
(keep=EmployeeID EmployeeHireDate Salary);
YearHired=year(EmployeeHireDate);
Average=yr{YearHired};
SalaryDif=Salary-Average;
run;

p13
75

In this program, the ARRAY statement creates the one-dimensional array yr,
which includes 34 elements, the numeric variables Yr1978 through Yr2011.

Notice that you're not assigning initial values to these array elements as you
did in the previous example. Assigning initial values is appropriate when you
have a small number of values or if the values rarely change. For this task,
you'll initialize the variables with the values from the SalaryStats data set, but
you're only interested in a single row where Statistic equals Avg_Salary.

75
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Using a One-Dimensional Array

Next is the lookup. This assignment statement retrieves the average salary
from the array yr at the appropriate position, and assigns the value to the
variable Average.
Finally, you calculate the salary difference.

76
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation Partial Listing of [Link]

Statistic yr1978 yr1979 yr1980 …
data compare; AvgSalary 39243.61 33037.5 39171.67 …
keep EmployeeID YearHired Salary
Average SalaryDif; Partial Listing of [Link]
format Salary Average SalaryDif
dollar12.2; Employee
EmployeeID Salary
array yr{1978:2011} Yr1978-Yr2011; HireDate
if _n_=1 then set [Link]
(where=(Statistic='AvgSalary')); 120101 163040 01JUL2007
set [Link] 120102 108255 01JUN1993
(keep=EmployeeID EmployeeHireDate
Salary); 120103 87975 01JAN1978
YearHired=year(EmployeeHireDate);
Average=yr{YearHired}; 120104 46230 01JAN1985
SalaryDif=Salary-Average;
run; 120105 27110 01MAY2003

Partial PDV
Salary Average SalaryDif

77 ...

This is processing is at the end of compile but before execution. Notice the
array is set up and the initial values loaded.

77
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif D Yr1978
SalaryDif Yr1978 D Yr1979
Yr1979 D Yr1980
Yr1980 D Yr1981
Yr1981 D Yr1982
Yr1982 …

. . . . . . . .
yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

DYr2007 D
Yr2008 D
Yr2009 D
Yr2010 D
Yr2011

78 ...

78
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif D Yr1978
SalaryDif Yr1978 D Yr1979
Yr1979 D Yr1980
Yr1980 D Yr1981
Yr1981 D Yr1982
Yr1982 …

. . . . . . . .
yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

DYr2007 D
Yr2008 D
Yr2009 D
Yr2010 D
Yr2011 Statistic

79 ...

79
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif D Yr1978
SalaryDif Yr1978 D Yr1979
Yr1979 D Yr1980
Yr1980 D Yr1981
Yr1981 D Yr1982
Yr1982 …

. . . . . . . .
yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

DYr2007 D D D D Employee
Yr2008 Yr2009 Yr2010 Yr2011 Statistic EmployeeIDD
HireDate

80 ...

80
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif D Yr1978
SalaryDif Yr1978 D Yr1979
Yr1979 D Yr1980
Yr1980 D Yr1981
Yr1981 D Yr1982
Yr1982 …

. . . . . . . .
yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

DYr2007 D D D D Employee Year

Yr2008 Yr2009 Yr2010 Yr2011 Statistic EmployeeIDD
HireDate Hired

81 ...

81
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation Partial Listing of [Link]

Partial PDV yr{1978} yr{1978}

yr{1979} yr{1980}
yr{1979} yr{1981}
yr{1980}yr{1982}
yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif D Yr1978
SalaryDif Yr1978 D Yr1979
Yr1979 D Yr1980
Yr1980 D Yr1981
Yr1981 D Yr1982
Yr1982 …

. . . . . . . .
yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

Employee Year
Yr2007 Yr2008 Yr2009 Yr2010 Yr2011 Statistic EmployeeID _N_
HireDate Hired

82 ...

82
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Compilation Partial Listing of [Link]

Partial PDV yr{1978} yr{1978}

yr{1979} yr{1980}
yr{1979} yr{1981}
yr{1980}yr{1982}
yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif DD Yr1978
SalaryDif Yr1978 DD Yr1979
Yr1979 DD Yr1980
Yr1980 DD Yr1981
Yr1981 DD Yr1982
Yr1982 …

. . . . . . . .
yr{2007}
yr{2008} yr{2008}
yr{2009} yr{2009}
yr{2010} yr{2010}
yr{2011} yr{2011}

83 ...

83
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif DD Yr1978
SalaryDif Yr1978 DD Yr1979
Yr1979 DD Yr1980
Yr1980 DD Yr1981
Yr1981 DD Yr1982
Yr1982 …

.. . . . . . . .
yr{2007}
yr{2008} yr{2008}
yr{2009}yr{2009}
yr{2010}yr{2010}
yr{2011}
yr{2011}

84 ...

84
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Statistic Yr1978 Yr1979 Yr1980 …
data compare; AvgSalary 39243.61 33037.5 39171.67 …
keep EmployeeID YearHired Salary
Average SalaryDif; Partial Listing of [Link]
format Salary Average SalaryDif
dollar12.2; Employee
EmployeeID Salary
array yr{1978:2011} Yr1978-Yr2011; HireDate
if _n_=1 then set [Link]
(where=(Statistic='AvgSalary')); 120101 163040 01JUL2007
set [Link] 120102 108255 01JUN1993
(keep=EmployeeID EmployeeHireDate
Salary); 120103 87975 01JAN1978
YearHired=year(EmployeeHireDate);
Average=yr{YearHired}; 120104 46230 01JAN1985
SalaryDif=Salary-Average;
run; 120105 27110 01MAY2003

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif D Yr1978
SalaryDif Yr1978 D Yr1979
Yr1979 D Yr1980
Yr1980 D Yr1981
Yr1981 D Yr1982
Yr1982 …

.. . . 39243.61 33037.5 39171.67 34170 37506.25

yr{2007}
yr{2008} yr{2008}
yr{2009}yr{2009}
yr{2010}yr{2010}
yr{2011}
yr{2011}

D StatisticEmployeeID Employee
Employee Year
Year D
D Yr2007
Yr2008 D Yr2008
Yr2009D Yr2009
Yr2010D Yr201 D Yr2011
Yr2011 Statistic EmployeeID D _N_
_N_
HireDate Hired
HireDate Hired
35082.5
29904.44 29904.44
30576.36 30576.36
27883.7127883.71
28861.67
28861.67
AvgSalary
AvgSalary . . . . .. 1

85 ...

85
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary
Salary Average
Average SalaryDif DD Yr1978
SalaryDif Yr1978 DD Yr1979
Yr1979 DD Yr1980
Yr1980 DD Yr1981
Yr1981 DD Yr1982
Yr1982 …
163040
163040 . . 39243.61 33037.5 39171.67 34170 37506.25

yr{2007}
yr{2008} yr{2008}
yr{2009}yr{2009}
yr{2010}yr{2010}
yr{2011}
yr{2011}

D StatisticEmployeeID Employee
Employee Year
Year D
DDYr2007
Yr2008 D Yr2008
D Yr2009D Yr2009
DYr2010
D Yr2010
DYr2011
D Yr2011
D Statistic EmployeeIDD D _N_
_N_
HireDate Hired
HireDate Hired
35082.5
29904.44 29904.44
30576.36 30576.36
27883.7127883.71
28861.67
28861.67
AvgSalary
AvgSalary 120101
120101 01JUL2007
01JUL2007 .. 1

86 ...

86
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary Average SalaryDif D Yr1978 D Yr1979 D Yr1980 D Yr1981 D Yr1982 …

163040 . . 39243.61 33037.5 39171.67 34170 37506.25

yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

D Statistic Employee Year D

D Yr2007 D Yr2008 D Yr2009 D Yr2010 D Yr2011 EmployeeID D _N_
HireDate Hired
35082.5 29904.44 30576.36 27883.71 28861.67 AvgSalary 120101 01JUL2007 2007 1

87 ...

87
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary Average SalaryDif D Yr1978 D Yr1979 D Yr1980 D Yr1981 D Yr1982 …

163040 . . 39243.61 33037.5 39171.67 34170 37506.25

yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

D Statistic Employee Year D

D Yr2007 D Yr2008 D Yr2009 D Yr2010 D Yr2011 EmployeeID D _N_
HireDate Hired
35082.5 29904.44 30576.36 27883.71 28861.67 AvgSalary 120101 01JUL2007 2007 1

88 ...

88
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Statistic Yr1978 Yr1979 Yr1980 …
data compare; AvgSalary 39243.61 33037.5 39171.67 …
keep EmployeeID YearHired Salary
Average SalaryDif; Partial Listing of [Link]
format Salary Average SalaryDif
dollar12.2; Employee
EmployeeID Salary
array yr{1978:2011} Yr1978-Yr2011; HireDate
if _n_=1 then set [Link]
(where=(Statistic='AvgSalary')); 120101 163040 01JUL2007
set [Link] 120102 108255 01JUN1993
(keep=EmployeeID EmployeeHireDate
Salary); 120103 87975 01JAN1978
YearHired=year(EmployeeHireDate);
Average=Yr{2007};
Average=yr{YearHired}; 120104 46230 01JAN1985
SalaryDif=Salary-Average;
run; 120105 27110 01MAY2003

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary Average SalaryDif D Yr1978 D Yr1979 D Yr1980 D Yr1981 D Yr1982 …

163040 35082.5 . 39243.61 33037.5 39171.67 34170 37506.25

yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

D Statistic Employee Year D

D Yr2007 D Yr2008 D Yr2009 D Yr2010 D Yr2011 EmployeeID D _N_
HireDate Hired
35082.5 29904.44 30576.36 27883.71 28861.67 AvgSalary 120101 01JUL2007 2007 1

89 ...

89
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary Average SalaryDif D Yr1978 D Yr1979 D Yr1980 D Yr1981 D Yr1982 …

163040 35082.5 127957.5 39243.61 33037.5 39171.67 34170 37506.25

yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

D Statistic Employee Year D

D Yr2007 D Yr2008 D Yr2009 D Yr2010 D Yr2011 EmployeeID D _N_
HireDate Hired
35082.5 29904.44 30576.36 27883.71 28861.67 AvgSalary 120101 01JUL2007 2007 1

90 ...

90
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Statistic Yr1978 Yr1979 Yr1980 …
data compare; AvgSalary 39243.61 33037.5 39171.67 …
keep EmployeeID YearHired Salary
Average SalaryDif; Partial Listing of [Link]
format Salary Average SalaryDif
dollar12.2; Employee
EmployeeID Salary
array yr{1978:2011} Yr1978-Yr2011; HireDate
if _n_=1 then set [Link]
(where=(Statistic='AvgSalary')); 120101 163040 01JUL2007
Implicit OUTPUT;
set [Link] 120102 108255 01JUN1993
(keep=EmployeeID EmployeeHireDate
Implicit RETURN;
Salary); 120103 87975 01JAN1978
YearHired=year(EmployeeHireDate);
Average=yr{YearHired}; 120104 46230 01JAN1985
SalaryDif=Salary-Average;
run; 120105 27110 01MAY2003

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary Average SalaryDif D Yr1978 D Yr1979 D Yr1980 D Yr1981 D Yr1982 …

163040 35082.5 127957.5 39243.61 33037.5 39171.67 34170 37506.25

yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

D Statistic Employee Year D

D Yr2007 D Yr2008 D Yr2009 D Yr2010 D Yr2011 EmployeeID D _N_
HireDate Hired
35082.5 29904.44 30576.36 27883.71 28861.67 AvgSalary 120101 01JUL2007 2007 1

91 ...

91
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Execution Partial Listing of [Link]

Partial PDV yr{1978} yr{1979} yr{1980} yr{1981} yr{1982}

Salary Average SalaryDif D Yr1978 D Yr1979 D Yr1980 D Yr1981 D Yr1982 …

163040 35082.5 127957.5 39243.61 33037.5 39171.67 34170 37506.25

yr{2007} yr{2008} yr{2009} yr{2010} yr{2011}

D Statistic Employee Year D

D Yr2007 D Yr2008 D Yr2009 D Yr2010 D Yr2011 EmployeeID D _N_
HireDate Hired
35082.5 29904.44 30576.36 27883.71 28861.67 AvgSalary 120101 01JUL2007 2007 1

92
C opy r i ght © 2014, SAS I nsti tute I nc . All r i ghts r eser ved.

Resulting Data
proc print data=compare(obs=5);
var EmployeeID YearHired Salary Average SalaryDif;
title 'Using One Dimensional Arrays';
run;

PROC PRINT Output

Using One Dimensional Arrays

Year
Obs EmployeeID Hired Salary Average SalaryDif

1 120101 2007 $163,040.00 $35,082.50 $127,957.50

2 120102 1993 $108,255.00 $88,588.75 $19,666.25
3 120103 1978 $87,975.00 $39,243.61 $48,731.39
4 120104 1985 $46,230.00 $36,436.67 $9,793.33
5 120105 2003 $27,110.00 $36,533.75 $-9,423.75

Array processing in Data Step

1 Introduction to Table Lookup Techniques

2 One-Dimensional Arrays

3 Two-Dimensional Arrays

4 Loading a Two-dimensional Array from a SAS Data Set

Objectives
◼ Define a two-dimensional array.
◼ Explain the differences between a one-dimensional
array and a two-dimensional array.

Two-Dimensional Arrays
Two-dimensional arrays has a row dimension and column
dimension.

Column

1,1 1,2
Row
2,1 2,2

A two-dimesional array is similar to the one-dmensional row of buckets but

each bucket is referred to with 2 numbers. As with a one-dimensional array,
you can think of a multidimensional array as rows of buckets. The difference is
that with a multidimensional array, you put a value in a bucket based on
multiple reference numbers, ...

Using Two-Dimensional Arrays

In this example, the first dimension is 2 rows and the
second dimension is 5 columns, so the array has a total of
10 elements.
array B{2,5} _temporary_
(1590000, 1880000, 2300000, 1960000, 1970000,
1290000, 1550000, 1830000, 1480000, 1640000);
ARRAY array-name {…,rows, cols} <$> <length>
<elements> <(initial values)>;

B{1,1} B{1,2} B{1,3} B{1,4} B{1,5}

B{2,1} B{2,2} B{2,3} B{2,4} B{2,5}

Using Two-Dimensional Arrays

The entire array must fit in memory at one time.

array B{2,5} _temporary_

(1590000, 1880000, 2300000, 1960000, 1970000,
1290000, 1550000, 1830000, 1480000, 1640000);

Array processing in Data Step

1 Introduction to Table Lookup Techniques

2 One-Dimensional Arrays

3 Two-dimensional Arrays

4 Loading a Two-dimensional Array from

a SAS Data Set

100

Objectives
◼ Load a two-dimensional array from a SAS data set.
◼ Identify the advantages of an array as a lookup table.

101

Business Scenario
Compare the actual profit values for each Orion Star
company to the budgeted profit values.

102

Business Scenario
This time, the budget values are stored in the
[Link] data set.

Partial [Link]
Month Yr2007 Yr2008 Yr2009 Yr2010 Yr2011
1 $1,590,000 $1,880,000 $2,300,000 $1,960,000 $1,970,000
2 $1,290,000 $1,550,000 $1,830,000 $1,480,000 $1,640,000
3 $1,160,000 $1,380,000 $1,640,000 $1,410,000 $1,440,000
4 $1,710,000 $2,100,000 $2,420,000 $2,130,000 $2,270,000
5 $1,990,000 $2,350,000 $2,840,000 $2,480,000 $2,670,000
.
.
.
12 $2,870,000 $3,120,000 $3,760,000 $3,210,000 $4,370,000

103

Loading an Array from a Data Set

When there are too many values to easily hardcode
in an array, an array can be loaded from a SAS data set.

104

The main reason for using a SAS data set to load the array is to avoid having
to type them. Array values should be read from a SAS data set when any of the
following conditions exist:
• There are too many values to initialize easily in the array.
• The values change frequently.
• The same values are used in many programs.

Using Multidimensional Arrays

data budgetamt;
drop Yr2007-Yr2011 Month Mon Yr Y M;
array B{12,2007:2011} _temporary_;
if _N_=1 then do Mon=1 to 12;
set [Link];
array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
B{Mon,Yr}=cols{Yr};
end;
end;
set [Link](where=(Sales ne .));
Y=year(YYMM);
M=month(YYMM);
BudgetAmt=B{M,Y};
run;
105 p14

Loading the Array

cols{2007} cols{2008} cols{2009} cols{2010} cols{2011}

106

Using Multidimensional Arrays

data budgetamt;
drop Yr2007-Yr2011 Month Mon Yr Y M;
array B{12,2007:2011} _temporary_;
if _N_=1 then do Mon=1 to 12;
set [Link];
array cols {2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
B{Mon,Yr}=cols{Yr};
end;
end;
set [Link](where=(Sales ne .));
Y=year(YYMM);
M=month(YYMM);
BudgetAmt=B{M,Y};
run;
107

Loading the Array

cols{2007}

B{1,2007}

108

This time we've got the array data in a data set where each observation is a
month and the YR variables represent years.

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{Mon,Yr}=cols{Yr};
end;
3 1160000 1380000 ... end;
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

. . .

Partial PDV cols{2007} cols{2008} cols{2009} cols{2010} cols{2011}

DMon D Month D Yr2007 D Yr2008 D Yr2009 D Yr2010 D Yr2011 D Yr Company
. . . . . . . .
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
109 ...

This is where processing is at the end of compilation. Notice that the array B
has no values in it, but it is temporary and not part of the PDV.

Execution data budgetamt;

. . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonthD DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 D DYr2010
Yr2010 DDYr2011
Yr2011 DD Yr Company
11 . . .. .. .. .. .. ..
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
110 ...

The ARRAY statement is not executable, that's why the highlighting skips to
the IF statement. Since the IF statement is true, the DO statement executes and
I is set to 1.

Execution data budgetamt;

. . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonthD DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 D DYr2010
Yr2010 DDYr2011
Yr2011 DD Yr Company
Company
11 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000
1970000 ..
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
111 ...

The first observation of the data set [Link] is loaded into the PDV.

Execution data budgetamt;

. . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
11 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000 2007
2007
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
112 ...

Yr is now 2007.

Execution data budgetamt;

. . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
11 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000 2007
2007
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
113 ...

The assignment statement loads the appropriate array element.

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{1,2007}=cols{2007};
B{Mon,Yr}=tmp{Yr};
end;
3 1160000 1380000 ... end;
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
11 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000 2007
2007
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
114 ...

The assignment statement uses values from the PDV to fill in the appropriate
array element with the value referenced by the array TMP.

Execution data budgetamt;

1590000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
11 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000 2008
2008
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
115 ...

Yr increments to 2008.

Execution data budgetamt;

1590000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
11 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000 2008
2008
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
116 ...

The subscript Yr is still in bounds, so the DO loop executes again.

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{1,2008}=cols{2008};
B{Mon,Yr}=tmp{Yr};
end;
3 1160000 1380000 ... end;
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 1880000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
11 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000 2008
2008
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
117 ...

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{Mon,Yr}=cols{Yr};
end;
3 1160000 1380000 ... end; Yr out
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
of bounds
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
.Continue until Yr=2012
... run;
. .

1590000 1880000 2300000 1960000 1970000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
12 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000 2012
2012
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
118 ...

Mon increments to 2. Control returns to the top of the outer DO loop.

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
22 11 1590000
1590000 1880000
1880000 2300000
2300000 1960000
1960000 1970000 2012
2012
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
119 ...

The outer DO statement executes a second time.

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
22 22 1290000
1290000 1550000
1550000 1830000
1830000 1480000
1480000 1640000 2012
2012
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
120 ...

The second observation from [Link] is copied into the PDV.

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
22 22 1290000
1290000 1550000
1550000 1830000
1830000 1480000
1480000 1640000 2007
2007
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
121 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 . . .

PartialPDV
Partial PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonth D DYr2007
DMonD Month
DMon Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010 DDYr2011
Yr2010
DD Yr2011 DD Yr Company
Company
22 22 1290000
1290000 1550000
1550000 1830000
1830000 1480000
1480000 1640000 2007
2007
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
122 ...

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{2,2007}=cols{2007};
B{Mon,Yr}=tmp{Yr};
end;
3 1160000 1380000 ... end;
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 1880000 2300000 1960000 1970000 1290000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
22 22 1290000
1290000 1550000
1550000 1830000
1830000 1480000
1480000 1640000 2007
2007
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
123 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
22 22 1290000
1290000 1550000
1550000 1830000
1830000 1480000
1480000 1640000 2008
2008
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
124 ...

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{2,2008}=cols{2008};
B{Mon,Yr}=tmp{Yr};
end;
3 1160000 1380000 ... end;
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
22 22 1290000
1290000 1550000
1550000 1830000
1830000 1480000
1480000 1640000 2008
2008
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
125 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
22 22 1290000
1290000 1550000
1550000 1830000
1830000 1480000
1480000 1640000 2009
2009
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
126 ...

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 Eventually,
1880000 ... Mon=12 array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 and Yr=2010
1550000 ... B{Mon,Yr}=cols{Yr};
end;
3 1160000 1380000 . . . end;
set [Link](where=(Sales ne .));
4 1710000 2100000 . . . Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1212 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2010
2010
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
127 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . .

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1212 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2010
2010
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
128 ...

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{12,2010}=cols{2010};
B{Mon,Yr}=tmp{Yr};
end;
3 1160000 1380000 ... end;
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1212 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2010
2010
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
129 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1212 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2011
2011
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
130 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1212 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2011
2011
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
131 ...

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{12,2011}=cols{2011};
B{Mon,Yr}=tmp{Yr};
end;
3 1160000 1380000 ... end;
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1212 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2011
2011
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
132 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1212 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012
2012
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
133 ...

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
2 1290000 1550000 ... B{Mon,Yr}=cols{Yr};
end;
3 1160000 1380000 ... end; Yr out
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
of bounds
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1313 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012
2012
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
134 ...

Execution data budgetamt;

drop Yr2007-Yr2011 Month Mon Yr Y M;
Partial [Link] array B{12,2007:2011} _temporary_;
Month Yr2007 Yr2008 ... if _N_=1 then do Mon=1 to 12;
set [Link];
1 1590000 1880000 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011; Mon out
2 1290000 1550000 ... B{Mon,Yr}=cols{Yr};
end; of bounds
3 1160000 1380000 ... end;
set [Link](where=(Sales ne .));
4 1710000 2100000 ... Y=year(YYMM);
M=month(YYMM);
. . . BudgetAmt=B{M,Y};
... run;
. . .

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1313 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012
2012
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
. . . . . . . . 1
135 ...

Execution data budgetamt;

Partial [Link] drop Yr2007-Yr2011 Month Mon Yr Y M;
array B{12,2007:2011} _temporary_;
Company YYMM Sales ... if _N_=1 then do Mon=1 to 12;
set [Link];
Logistics 07M01 457809 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
Logistics 07M02 325138 ... B{Mon,Yr}=cols{Yr};
end;
Logistics 07M03 276805 ... end;
set [Link](where=(Sales ne .));
Logistics 07M04 558806 ... Y=year(YYMM);
. . . M=month(YYMM);
... BudgetAmt=B{M,Y};
. . . run;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1313 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012
2012 Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M01 457809 210914 127525 119370 . . . 1
136 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1313 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012
2012 Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M01 457809 210914 127525 119370 2007 1 . 1
137 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1313 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012
2012 Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M01 457809 210914 127525 119370 2007 1 . 1
138 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1313 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012
2012 Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M01 457809 210914 127525 119370 2007 1 1590000 1
139 ...

Execution data budgetamt;

Partial [Link] drop Yr2007-Yr2011 Month Mon Yr Y M;
array B{12,2007:2011} _temporary_;
Company YYMM Sales ... if _N_=1 then do Mon=1 to 12;
set [Link];
Logistics 07M01 457809 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
Logistics 07M02 325138 ... B{Mon,Yr}=cols{Yr};
end;
Logistics 07M03 276805 ... end;
set [Link](where=(Sales ne .));
Logistics 07M04 558806 ... Implicit OUTPUT;
Y=year(YYMM);
. . . M=month(YYMM);
Implicit RETURN;
... BudgetAmt=B{M,Y};
. . . run;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
1313 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012
2012 Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M01 457809 210914 127525 119370 2007 1 1590000 1
140 ...

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonDDMonth
DDMon DMonth DD DYr2007
Yr2007 DDDYr2008
Yr2008 DDDYr2009
Yr2009 Yr2010
DDDYr2010 DDDYr2011
Yr2011 DD Yr Company
Company
. . 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012. Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M01 457809 210914 127525 119370 . . . 2
141 ...

Execution False
data budgetamt;
Partial [Link] drop Yr2007-Yr2011 Month Mon Yr Y M;
array B{12,2007:2011} _temporary_;
Company YYMM Sales ... if _N_=1 then do Mon=1 to 12;
set [Link];
Logistics 07M01 457809 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
Logistics 07M02 325138 ... B{Mon,Yr}=cols{Yr};
end;
Logistics 07M03 276805 ... end;
set [Link](where=(Sales ne .));
Logistics 07M04 558806 ... Y=year(YYMM);
. . . M=month(YYMM);
... BudgetAmt=B{M,Y};
. . . run;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
. . 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 2012. Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M02 457809 210914 127525 119370 . . . 2
142 ...

The IF/THEN DO group does not execute again because _N_ is 2. That is
important to prevent SAS from hitting the end of file marker on [Link]
and ending the DATA step.

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
. . 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 .. Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M02 325138 149718 127525 47895 . . . 2
143 ...

The second SET statement executes again, and the second observation of
[Link] is copied into the PDV.

Execution data budgetamt;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
. . 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 .. Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M02 325138 149718 127525 47895 2007 2 1290000 2
144 ...

Y and M are calculated. BudgetAmt is assigned a value

Execution data budgetamt;

Partial [Link] drop Yr2007-Yr2011 Month Mon Yr Y M;
array B{12,2007:2011} _temporary_;
Company YYMM Sales ... if _N_=1 then do Mon=1 to 12;
set [Link];
Logistics 07M01 457809 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
Logistics 07M02 325138 ... B{Mon,Yr}=cols{Yr};
end;
Logistics 07M03 276805 ... end;
set [Link](where=(Sales ne .));
Logistics 07M04 558806 ... Implicit OUTPUT;
Y=year(YYMM);
. . . M=month(YYMM);
... Implicit RETURN;
BudgetAmt=B{M,Y};
. . . run;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
. . 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 .. Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M02 325138 149718 127525 47895 2007 2 1290000 2
145 ...

Bottom of the DATA step; implicit output and implicit return.

Execution data budgetamt;

Partial [Link] drop Yr2007-Yr2011 Month Mon Yr Y M;
array B{12,2007:2011} _temporary_;
Company YYMM Sales ... if _N_=1 then do Mon=1 to 12;
set [Link];
Logistics 07M01 457809 ... array cols{2007:2011} Yr2007-Yr2011;
do Yr=2007 to 2011;
Logistics 07M02 325138 ... B{Mon,Yr}=cols{Yr};
Logistics 07M03 Continue
276805 . . . until EOF in end;
[Link].
end;
set [Link](where=(Sales ne .));
Logistics 07M04 558806 ... Y=year(YYMM);
. . . M=month(YYMM);
... BudgetAmt=B{M,Y};
. . . run;

1590000 1880000 2300000 1960000 1970000 1290000 1550000 . . . 3210000 4370000

Partial
PartialPDV
PDV cols{2007}
tmp{2007} cols{2008}
tmp{2008} cols{2009} tmp{2010} cols{2011}
tmp{2009} cols{2010} tmp{2011}
DMonD Month
DMon DMonth D DYr2007
Yr2007 D DYr2008
Yr2008 D DYr2009
Yr2009 Yr2010
DDYr2010 DDYr2011
Yr2011 DD Yr Company
Company
. . 1212 2870000
2870000 3120000
3120000 3760000
3760000 3210000
3210000 4370000 .. Logistics
Budget
YYMM Sales Cost Salaries Profit D Y D M D _N_
Amt
07M02 325138 149718 127525 47895 2007 2 1290000 2
146

And execution continues until the end of the file marker in [Link].

147

Understanding SAS Arrays and Usage
No ratings yet
Understanding SAS Arrays and Usage
10 pages
Basics of SAS Array Processing
No ratings yet
Basics of SAS Array Processing
3 pages
Sascheatsheet 170401221255
100% (1)
Sascheatsheet 170401221255
29 pages
SAS Data Management and Procedures Guide
No ratings yet
SAS Data Management and Procedures Guide
13 pages
Understanding SAS Array Statements
No ratings yet
Understanding SAS Array Statements
7 pages
Mastering SAS Programming Essentials
No ratings yet
Mastering SAS Programming Essentials
34 pages
Using Arrays in SAS Programming
No ratings yet
Using Arrays in SAS Programming
24 pages
SAS Programming Cheat Sheet
No ratings yet
SAS Programming Cheat Sheet
4 pages
Sas Arrays Detailed Study Notes Expanded
No ratings yet
Sas Arrays Detailed Study Notes Expanded
17 pages
SAS Programming Cheat Sheet
No ratings yet
SAS Programming Cheat Sheet
24 pages
Creating Reports with PROC PRINT in SAS
No ratings yet
Creating Reports with PROC PRINT in SAS
12 pages
Sas Arrays Detailed Study Notes Expanded
No ratings yet
Sas Arrays Detailed Study Notes Expanded
9 pages
SAS Library and Data Management Guide
No ratings yet
SAS Library and Data Management Guide
8 pages
PROC MEANS and FREQ Report Guide
No ratings yet
PROC MEANS and FREQ Report Guide
15 pages
Learn SAS Basics in 50 Minutes
No ratings yet
Learn SAS Basics in 50 Minutes
78 pages
SAS Programming Basics and Syntax Guide
No ratings yet
SAS Programming Basics and Syntax Guide
8 pages
Essential SAS Procedures Guide
No ratings yet
Essential SAS Procedures Guide
7 pages
SAS Software Overview and Components
No ratings yet
SAS Software Overview and Components
20 pages
SAS Data Management Techniques
100% (2)
SAS Data Management Techniques
50 pages
Last Year Paper Solution BDSQL
No ratings yet
Last Year Paper Solution BDSQL
13 pages
SAS Programming Notes for Econometrics
No ratings yet
SAS Programming Notes for Econometrics
88 pages
Handbook of SAS DATA Step Programming 1st Edition Arthur Li Full Access
100% (1)
Handbook of SAS DATA Step Programming 1st Edition Arthur Li Full Access
100 pages
SAS Programming Guide and Procedures
No ratings yet
SAS Programming Guide and Procedures
4 pages
Tips and Techniques For The SAS Programmer
No ratings yet
Tips and Techniques For The SAS Programmer
19 pages
SAS Programming Notes for Econometrics
No ratings yet
SAS Programming Notes for Econometrics
88 pages
SAS Arrays for Longitudinal Data Analysis
No ratings yet
SAS Arrays for Longitudinal Data Analysis
15 pages
Essential SAS Data Manipulation Commands
No ratings yet
Essential SAS Data Manipulation Commands
17 pages
Understanding SAS Arrays and Usage
No ratings yet
Understanding SAS Arrays and Usage
20 pages
Quick Guide to SAS Programming
No ratings yet
Quick Guide to SAS Programming
19 pages
Create SAS Data Sets from External Files
No ratings yet
Create SAS Data Sets from External Files
10 pages
SAS Procedures Overview and Applications
No ratings yet
SAS Procedures Overview and Applications
8 pages
Data Preparation Techniques in SAS
No ratings yet
Data Preparation Techniques in SAS
55 pages
Summary of Lesson 4: Producing Detail Reports: Topic Summaries
No ratings yet
Summary of Lesson 4: Producing Detail Reports: Topic Summaries
4 pages
Learning PROC REPORT Basics
No ratings yet
Learning PROC REPORT Basics
9 pages
Intro to SAS: Basics and Functions
100% (2)
Intro to SAS: Basics and Functions
86 pages
Data Transformations in SAS
No ratings yet
Data Transformations in SAS
31 pages
SAS Data Manipulation Techniques
No ratings yet
SAS Data Manipulation Techniques
31 pages
Moving Average in SAS Basics
No ratings yet
Moving Average in SAS Basics
14 pages
Overview of SAS for Data Analysis
No ratings yet
Overview of SAS for Data Analysis
34 pages
Importing SAS Datasets with INFILE
No ratings yet
Importing SAS Datasets with INFILE
30 pages
Data Modification
No ratings yet
Data Modification
43 pages
Changing The Shape of Your Data: PROC TRANSPOSE vs. Arrays: Bob Virgile Robert Virgile Associates, Inc
No ratings yet
Changing The Shape of Your Data: PROC TRANSPOSE vs. Arrays: Bob Virgile Robert Virgile Associates, Inc
6 pages
Understanding SAS Data Libraries and Datasets
No ratings yet
Understanding SAS Data Libraries and Datasets
44 pages
Introduction to the SAS System
100% (1)
Introduction to the SAS System
84 pages
Summary of Lesson 4: Producing Detail Reports: Topic Summaries
No ratings yet
Summary of Lesson 4: Producing Detail Reports: Topic Summaries
4 pages
Kaz SAS Manual Basic
No ratings yet
Kaz SAS Manual Basic
30 pages
Lacture 4
No ratings yet
Lacture 4
8 pages
Techniques for Reading Raw Data in SAS
No ratings yet
Techniques for Reading Raw Data in SAS
7 pages
SAS Print and Data Options Guide
No ratings yet
SAS Print and Data Options Guide
15 pages
Unit 6 - Reading SAS (R) Data Sets
No ratings yet
Unit 6 - Reading SAS (R) Data Sets
106 pages
Based On Learning SAS by Example: A Programmer's Guide: Chapters 3 & 4
No ratings yet
Based On Learning SAS by Example: A Programmer's Guide: Chapters 3 & 4
52 pages
SAS Programming Guide for Data Analysis
No ratings yet
SAS Programming Guide for Data Analysis
30 pages
Understanding SAS Arrays and Syntax
No ratings yet
Understanding SAS Arrays and Syntax
14 pages
SAS Programming Skills
100% (1)
SAS Programming Skills
19 pages
SAS Correlation and Data Formatting Guide
No ratings yet
SAS Correlation and Data Formatting Guide
6 pages
Applied Statistics and The SAS Programming 5th Edition
0% (2)
Applied Statistics and The SAS Programming 5th Edition
44 pages
SAS Procedures for Advanced Analytics
No ratings yet
SAS Procedures for Advanced Analytics
14 pages
SAS Data Processing and Reporting Guide
No ratings yet
SAS Data Processing and Reporting Guide
6 pages
Top 10 SAS Questions Explained
No ratings yet
Top 10 SAS Questions Explained
8 pages
Mydin CRM Training and Engagement Strategies
No ratings yet
Mydin CRM Training and Engagement Strategies
18 pages
Fatigue Studies on Carbon Steel Piping
No ratings yet
Fatigue Studies on Carbon Steel Piping
32 pages
Revised Locker Policy for FY 2023-24
No ratings yet
Revised Locker Policy for FY 2023-24
1 page
DCCN Lab Assignment With Diagrams
No ratings yet
DCCN Lab Assignment With Diagrams
25 pages
Tenova Delkor: Advanced Jig Technology
No ratings yet
Tenova Delkor: Advanced Jig Technology
6 pages
E-Commerce: A Historical Overview
No ratings yet
E-Commerce: A Historical Overview
17 pages
Classification of Microorganisms in Sanitary Microbiology
No ratings yet
Classification of Microorganisms in Sanitary Microbiology
11 pages
Grade 4 Term 2 NST Test Paper
50% (2)
Grade 4 Term 2 NST Test Paper
5 pages
EDDM Munich Airport Operations Guide
100% (1)
EDDM Munich Airport Operations Guide
76 pages
Budget & Economic Survey Overview 2022-23
No ratings yet
Budget & Economic Survey Overview 2022-23
111 pages
IAESTE Exam Practice: English Proficiency
No ratings yet
IAESTE Exam Practice: English Proficiency
6 pages
English - Grade1 - Q4 - SIM 25 Lesson 3 v.4
No ratings yet
English - Grade1 - Q4 - SIM 25 Lesson 3 v.4
12 pages
Review of Al-Shalawi CARs Status
No ratings yet
Review of Al-Shalawi CARs Status
9 pages
Summer Holidays Project Work For Class 6
No ratings yet
Summer Holidays Project Work For Class 6
2 pages
Lesson Plan on Asteroids and Comets
No ratings yet
Lesson Plan on Asteroids and Comets
6 pages
Sugarfina v. Bouquet Bar - Motion To Dismiss
No ratings yet
Sugarfina v. Bouquet Bar - Motion To Dismiss
106 pages
Compiler Reference Guide 101754 0615 01 en
No ratings yet
Compiler Reference Guide 101754 0615 01 en
1,206 pages
Barnes Molding & Casting Products
100% (1)
Barnes Molding & Casting Products
68 pages
Mahindra Centuro Owner's Guide
No ratings yet
Mahindra Centuro Owner's Guide
91 pages
Muscle Composition: Water, Protein, Lipids
No ratings yet
Muscle Composition: Water, Protein, Lipids
3 pages
Myasthenia Gravis
No ratings yet
Myasthenia Gravis
19 pages
SSQ 162 203
No ratings yet
SSQ 162 203
3 pages
Đại Từ và Tính Từ Sở Hữu trong Tiếng Việt
No ratings yet
Đại Từ và Tính Từ Sở Hữu trong Tiếng Việt
13 pages
MI 34 Tympanometer Operating Guide
No ratings yet
MI 34 Tympanometer Operating Guide
51 pages
Purchase Order for Herbal Teas and Capsules
No ratings yet
Purchase Order for Herbal Teas and Capsules
2 pages
Zalgo's Ritual and Randy's Return
No ratings yet
Zalgo's Ritual and Randy's Return
7 pages
Industrial Revolution Lesson Plan
No ratings yet
Industrial Revolution Lesson Plan
5 pages
Integration for Motion and Acceleration
No ratings yet
Integration for Motion and Acceleration
25 pages
Analytical Chemistry
No ratings yet
Analytical Chemistry
19 pages
7th Grade Math Standards Unpacked Guide
100% (1)
7th Grade Math Standards Unpacked Guide
45 pages