Sometimes raw data files contain data for several observations in one record. Data is stored in this manner to reduce the size of the entire data file.

Each record can contain

This chapter shows you several ways of creating multiple observations from a single record.

In this chapter, you learn to

Additionally, you learn to

As you begin to write the INPUT statement, you need to hold the current record until each block of data has been read and written to the data set as an observation. This is easily accomplished by using a line-hold specifier in the INPUT statement.

SAS provides two line-hold specifiers.

The term trailing indicates that the @ or @@ must be the last item specified in the INPUT statement. For example,

input Name $20. @; or input Name $20. @@;

This chapter teaches you how the trailing @@ can be used to hold a record across multiple iterations of the DATA step.

Normally, each time a DATA step executes, the INPUT statement reads a new record. But when you use the trailing @@, the INPUT statement holds the current record and reads the next value.

The double trailing at sign (@@)

A record that is being held by the double trailing at sign (@@) is not released until either of the following events occurs:

This example requires only one INPUT statement to read the values for Date and HighTemp, but the INPUT statement must execute three times for each record.

The INPUT statement reads a block of values for Date and HighTemp, and then holds the current record by using the trailing @@. The values in the program data vector are written to the data set as an observation, and control returns to the top of the DATA step.

In the next iteration, the INPUT statement reads the next block of values for Date and HighTemp from the same record.

You can add a FORMAT statement to the DATA step to display the date or time values with a specified format in the data set. In the FORMAT statement below, the DATEw. format is used to display the values for Date in the form ddmmmyyyy.

Here is the complete DATA step.

data perm.april90;
      infile tempdata;
      input Date : date. HighTemp @@;
      format date date9.;
run;

Example

As the execution phase begins, the input pointer rests on column 1 of record 1.

During the first iteration of the DATA step, the first block of values for Date and High Temp are read and stored in the program data vector.

The first observation is written to the data set, control returns to the top of the DATA step, and the values are reset to missing.

During the second iteration, the @@ prevents the input pointer from moving down to column 1 of the next record.

The INPUT statement reads the second block of values for Date and HighTemp in the first record.

The second observation is written to the data set, and control returns to the top of the DATA step.

During the third iteration, the last block of values is read and written to the data set as the third observation.

During the fourth iteration, the first block of values in the second record is read and written as the fourth observation.

The execution phase continues until the last block of data is read.

You can display the data set with the PRINT procedure.

First, you need to read the value for ID and hold the record so that subsequent values for Sales can be read.

You are already familiar with the trailing @@, which holds the current record across multiple iterations of the DATA step.

However, in this case, you want to hold the record with the trailing @ line hold specifier, so that a second INPUT statement can read the values for Sales within the same iteration of the DATA step. Like the trailing @@, the single trailing @

It's easy to distinguish between the trailing @@ and the trailing @ by remembering that

In this example, the first INPUT statement reads the value for ID and uses the trailing @ to hold the current record for the next INPUT statement in the DATA step.

The second INPUT statement reads a value for Sales and holds the record. The COMMAw.d informat in the INPUT statement reads the numeric value for Sales and removes the embedded commas. An OUTPUT statement writes the observation to the SAS data set, and the DATA step continues processing.

When all of the repeating fields have been read and output, control returns to the top of the DATA step, and the record is released.

Each record contains four different values for the variable Sales, so the INPUT statement must execute four times. Rather than writing four INPUT statements, you can execute one INPUT statement repeatedly in an iterative DO loop.

Each time the loop executes, you need to write the values for ID, Quarter, and Sales as an observation to the data set. This is easily accomplished by using the OUTPUT statement.

data perm.sales97;
      infile data97;
      input ID $ @;
      do Quarter=1 to 4;
      input Sales : comma. @;
      output;
      end;
run;

By default, every DATA step contains an implicit OUTPUT statement at the end of the step. Placing an explicit OUTPUT statement in a DATA step overrides the automatic output, and SAS adds an observation to a data set only when the explicit OUTPUT statement is executed.

Now that the program is complete, let's see how SAS processes a DATA step that contains an iterative DO loop.

data perm.sales97;
   infile data97;
   input ID $ @;
   do Quarter=1 to 4;
      input Sales : comma. @;
      output;
   end;
run;

During the first iteration, the value for ID is read and Quarter is initial to 1, so the loop begins to execute.

The INPUT statement reads the first repeating field and assigns the value to Sales in the program data vector. The @ holds the current record.

The OUTPUT statement writes the values in the program data vector to the data set as the first observation.

The END statement indicates the bottom of the loop, but control returns to the DO statement, not to the top of the DATA step. Now the value of Quarter is incremented to 2.

The INPUT statement executes again, reading the second repeating field and storing the value for Sales in the program data vector.

The OUTPUT statement writes the values in the program data vector as the second observation.

The loop continues executing while the value for Quarter is 3, then 4. In the process, the third and fourth observations are created.

After the fourth observation is created, Quarter is incremented to 5 at the bottom of the DO loop and control returns to the top of the loop. The loop does not execute again because the value of Quarter is now greater than 4.

Now the RUN statement executes. Control returns to the top of the DATA step, and the input pointer moves to column 1 of the next record. The variable values in the program data vector are reset to missing.

When the execution phase is complete, you can display the data set with the PRINT procedure.

You can adapt the DATA step to accommodate a varying number of values for Sales.

Like the previous example with the same number of repeating fields, your DATA step must read the same record more than once. However, you need to prevent the input pointer from moving to the next record when there are missing values for Sales.

You can use the MISSOVER option in an INFILE statement to prevent SAS from reading the next record when missing values are encountered at the end of a record. Essentially, records that have a varying number of repeating fields are records that contain missing values, so you need to specify the MISSOVER option here as well.

Because there is at least one value for the repeating field, Sales, in each record, the first INPUT statement reads both the value for ID and the first value for Sales in the first record. The trailing @ holds the record so that any subsequent repeating fields can be read.

Other related options include FLOWOVER (the default), STOPOVER, and SCANOVER. For more information about TRUNCOVER and related options, see the SAS documentation.

Now consider how many times to read each record. Earlier, you created an index variable named Quarter whose value ranged from 1 to 4 because there were four repeating fields.

Now you want to read the record only while a value for Sales exists. Use a DO WHILE statement instead of the iterative DO statement, enclosing the expression in parentheses. In the example below, the DO WHILE statement executes while the value of Sales is not equal to a missing value (which is represented by a period).

data perm.sales97;
   infile data97 missover;
   input ID $ Sales : comma. @;
   do while (sales ne .);

Because the DO WHILE statement does not create an index variable, you can create your own "counter" variable. You can then use a sum statement to increment the value of the counter variable each time the DO WHILE loop executes.

In the example below, the assignment statement that precedes the loop creates the counter variable Quarter and assigns it an initial value of zero. Each time the DO WHILE loop executes, the sum statement increments the value of Quarter by one.

data perm.sales97;
   infile data97 missover;
   input ID $ Sales : comma. @;
   Quarter=0;
   do while (sales ne .);
   quarter+1;

Now look at the other statements that should be executed in the DO WHILE loop. First, you need an OUTPUT statement to write the current observation to the data set. Then, another INPUT statement reads the next value for Sales and holds the record. You complete the DO WHILE loop with an END statement.

data perm.sales97;
   infile data97 missover;
   input ID $ Sales : comma. @;
   Quarter=0;
   do while (sales ne .);
      quarter+1;
      output; 
      input sales : comma. @; 
      end;
run;

Here is the new version of the DATA step.

data perm.sales97;
   infile data97 missover;
   input ID $ Sales : comma. @;
   Quarter=0;
   do while (sales ne .);
      quarter+1;
      output;
      input sales : comma. @;
   end;
run;

During the first iteration of the DATA step, values for ID and Sales are read. Quarter is initialized to 0.

The DO WHILE statement checks to see if Sales has a value, which it does, so the other statements in the DO loop execute. The Value of Quarter is incremented by 1 and the current observation is written to the data set.

The INPUT statement reads the next value for Sales, the end of the loop is reached, and control returns to the DO WHILE statement.

The condition is checked and Sales still has a value, so the loop executes again.

Quarter is incremented to 2, and the values in the program data vector are written as the second observation.

The MISSOVER option prevents the input pointer from moving to the next record in search of another value for Sales. At this point, Sales has no value.

Because the condition is now false, the statements in the loop are not executed.

Instead, control returns to the top of the DATA step, the values in the program data vector are reset to missing, and the input pointer moves to column 1 of the next record. The DATA step continues executing until all of the values for Sales are read.

PROC PRINT output for the data set shows a varying number of observations for each employee.