Raw data files can be hierarchical in structure, consisting of a header record and one or more detail records. Typically, each record contains a field that identifies the record type.

Here, the P indicates a header record that contains a patient's ID number. The C indicates a detail record that contains the date of the patient's appointment and the charges that the patient has incurred.

You can build a SAS data set from a hierarchical file by creating one observation per detail record and storing each header record as part of the observation.

You can also build a SAS data set from a hierarchical file by creating one observation per header record and combining the information from detail records into summary variables.

In this chapter, you learn how to read from a hierarchical file and create a SAS data set that contains either one observation for each detail record or one observation for each header record.

In this chapter, you learn to

You can also review how to

As you write the DATA step to read this file, remember that you want to keep the header record as a part of each observation until the next header record is encountered. To do this, you need to use a RETAIN statement to retain the values for Address across iterations of the DATA step.

Next, you need to read the first field in each record, which identifies the record's type. You also need to use the @ line-hold specifier to hold the current record so that the other values in the record can be read.

You can use the value of type to identify each record. If type is H, you need to execute an INPUT statement to read the values for Address. However, if type is P, then execute an INPUT statement to read the values for Name, Age, and Gender.

You can tell SAS to perform a given task based on a specific condition by using an IF-THEN statement.

Expressions in conditional statements usually involve some kind of comparison. In the example shown above, type is being compared to a value that is found in the raw data. When the condition is met, the expression is evaluated as true, and the statement that follows the keyword THEN is executed.

The expression defines a condition so that when the value of type is H, the INPUT statement reads the values for Address. However, when the value of type is not H, the expression is evaluated as false, and the INPUT statement is not executed. Notice that the value is enclosed in quotation marks because it is a character value.

if type='h' then ... ;

Now think about what needs to happen when a detail record is read. Remember, you want to write an observation to the data set only when the value of type is P.

You can use a subsetting IF statement to check for the condition that type is P. The remaining DATA step statements execute only when the condition is true. If type is not P, then the values for Name, Age, and Gender are not read, the values in the program data vector are not written to the data set as an observation, and control returns to the top of the DATA step.

The values for Address are retained only as a part of the observation. Remember, you do not want to create an observation for each header record.

data perm.people;
   infile census;

   retain Address;
   input type $1. @;
   if type='H' then input @3 address $15.;
   if type='P'; 
   input @3 Name $10. @13 Age 3. @16 Gender $1.;
run;

Because type is useful only for identifying a record's type, drop the variable from the data set. The DROP= option in the DATA statement shown here prevents the values of type from being written to the data set.

data perm.people (drop=type);
   infile census;
   retain Address;
   input type $1. @;
   if type='H' then input @3 address $15.;
   if type='P';
   input @3 Name $10. @13 Age 3. @16 Gender $1.;
run;

Let's take a look at how this DATA step is processed.

data perm.people (drop=type);
   infile census;
   retain Address;
   input type $1. @;
   if type='H' then input @3 address $15.;
   if type='P';
   input @3 Name $10. @13 Age 3. @16 Gender $1.;
run;

At compile time, the variable type is flagged so that its values are not written to the data set. Address is flagged so that its value is retained across iterations of the DATA step.

As the DATA step begins to execute, the INPUT statement reads the value for type and holds the first record.

The condition type='H' is checked and found to be true, so the INPUT statement reads the value for Address in the first record.

Next, the subsetting IF statement checks for the condition type='P'. Because the condition is not true, the remaining statements are not executed and control returns to the top of the DATA step.

As the second iteration begins, the input pointer moves to the next record and a new value for type is read. The condition expressed in the IF-THEN statement is not true, so the statement following the THEN keyword is not executed.

Now the subsetting IF statement checks for the condition type='P'. In this iteration, the condition is true, so the remaining INPUT statement is executed.

The INPUT statement reads the values for Name, Age, and Gender.

Then the values in the program data vector are written as the first observation, and control returns to the top of the DATA step. Notice that the values for type are not included.

As the execution phase continues, observations are produced from the third and fourth records. However, notice that the fifth record is a header record. During the fifth iteration, the condition type='H' is true, so the new value for Address is read into the program data vector.

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

To execute multiple SAS statements based on the value of a variable, you can use a simple DO group with an IF-THEN statement. When the condition type='H' is true, several statements need to be executed.

data perm.residnts;
   infile census;
   retain Address;
   input type $1. @;
   if type='H' then do;

When the value of type is not H, you need to define an alternative action. You can do this by adding an ELSE statement to the IF-THEN statement.

Remember that the IF-THEN statement executes a SAS statement when the condition specified in the IF clause is true. By adding an ELSE statement after the IF-THEN statement, you define an alternative action to be performed when the THEN clause is not executed.

data perm.residnts;
   infile census;
   retain Address;
   input type $1. @;
   if type='H' then do;
      if _n_ > 1 then output;
      Total=0;
      input address $ 3-17;
end;
else

The only other type of record is a detail record, represented by a P. You want to count each person who is represented by a detail record and store the accumulated value in the summary variable Total. You do not need to read the values for Name, Age, and Gender.

You have already initialized the value of Total to 0 each time a header record is read. Now, as each detail record is read, you can increment the value of Total by using a sum statement. In this example you're counting the number of detail records for each header record, so you increment the value of Total by 1 when the value of type is P.

data perm.residnts;
   infile census;
   retain Address;
   input type $1. @;
   if type='H' then do;
      if _n_ > 1 then output;
      Total=0;
      input address $ 3-17;
end;
else if type='P' then total+1;

else if type='B' then total+cost;

Your program writes an observation to the data set only when another header record is read and the DATA step has executed more than once. But after the last detail record is read, there are no more header records to cause the last observation to be written to the data set.

You need to determine when the last record in the file is read so that you can then execute another explicit OUTPUT statement. You can determine when the current record is the last record in an external file by specifying the END= option in the INFILE statement. (You learned how to use the END= option with a SET statement if you completed Chapter 12, "Reading SAS Data Sets," on page 345.)

In the following example, the END= variable is defined in the INFILE statement as last. When last has a value other than 0, the OUTPUT statement writes the last observation to the data set.

data perm.residnts;
   infile census end=last;
   retain Address;
   input type $1. @;
   if type='H' then do;
    if _n_ > 1 then output;
       Total=0;
      input address $ 3-17;
   end;
   else if type='P' then total+1;
   if last then output;

A DROP= option in the DATA statement drops the variable type from the data set, and a RUN statement completes the DATA step.

data perm.residnts (drop=type);
   infile census end=last;
   retain Address;
   input type $1. @;
   if type='H' then do;
      if _n_ > 1 then output;
         Total=0;
      input address $ 3-17;
   end;
   else if type='P' then total+1;
   if last then output;
run;