Sentry Page Protection
Data Manipulation [16-18]
Retain Statement
The Retain statement retains a value from one observation to the next.
The REVENUE data set contains the weekly revenue for 10 weeks.
Let's take a look at this data step:
Example
Data Revenue2;
Set Revenue;
if _n_ = 1 then Total = 0;
Run;
The variable TOTAL is created with an initial value of 0.
Now, let's take a look an example with the Retain statement.
Example
Data Revenue2;
Set Revenue;
Retain Total;
if _n_ = 1 then Total = 0;
Run;
The Retain statement retains the value of "0" from the first observation to the next.
The first observation from TOTAL is assigned the value of "0".
This value retains to the next observation and as a result, the entire column of TOTAL consists of "0".
Cumulative Summation
The main application of the retain statement is to perform cumulative summation.
Example
Data Revenue3;
Set Revenue;
Retain Total;
if _n_ = 1 then Total = 0;
Total = sum(revenue, total);
Run;
Example
Data Revenue3;
Set Revenue;
Retain Total;
if _n_ = 1 then Total = 0;
Total = sum(revenue, total);
Run;
With the use of the Retain statement, you can compute the summation across observations.
In our example, the initial value of Total is set as zero:
If _n_ = 1 then Total = 0;
However, it is immediately added the revenue from week1.
Total = Sum(Total, Revenue);
The week1 (total) revenue is retained to the next observation, which is week2.
The week2 revenue is then added to the total revenue before being retained to week3.
The summation continues until the last observation.
In the end, the last observation contains the sum of revenues across all observations (i.e. $69,800).
Note: setting the initial value to the variable TOTAL is actually not required.
This will be explained in the next session.
Exercise
Copy and run the INVENTORY data set from the yellow box below:
Copy and run the INVENTORY data set from the yellow box below:
The INVENTORY data set contains the inventory of a bookshelf from a furniture store.
Compute the total inventory across all of the stores.
Need some help?
HINT:
Simply use the code covered in this session as a template. It should get you the correct result.
SOLUTION:
Data Inventory2;
Set Inventory;
Retain Total;
if _n_ = 1 then Total = 0;
Total = sum(QTY, total);
Run;
or
Data Inventory2;
Set Inventory;
Retain Total;
Total = sum(QTY, total);
Run;
Fill out my online form.