Subsetting records from an external file with a subsetting IF statement

Records from an external file can be subset by specifying a subsetting IF statement. The subsetting IF first reads a record from an external file into memory and then determines whether the condition is true. It cannot be used in a PROC step. In the following example, a subset of patients 65 years or older are written to the SENIORS data set.

DATA SENIORS;
  INFILE "Patients.dat"  MISSOVER;
  INPUT @22 AGE 3. @;
  IF AGE > 64;
  INPUT @1 NAME  $20.
       @26 GENDER $1.;
RUN;

Subsetting observations in a DATA step with a WHERE statement

Observations from an existing SAS data set can be subset with a WHERE statement in a DATA step. This approach specifies a WHERE statement instead of a subsetting IF statement to select observations. It works by first evaluating whether the specified WHERE condition is true before an observation is read into the Program Data Vector (PDV) for processing. Any observations not meeting the WHERE condition are automatically eliminated from being read into the PDV. In the next example a subset of patients 65 years or older are written to the SENIORS data set.

DATA SENIORS;
  SET PATIENTS;
  WHERE AGE > 64;   
RUN;

Subsetting observations in a PROC step with a WHERE statement

Observations can also be subset with a WHERE statement in a PROC step. Essentially a WHERE statement works the same way as when specified in a DATA step, except without the creation of a new data set subset. In the next example a subset of patients 65 years or older are selected for output in a PROC PRINT.

PROC PRINT
        DATA=PATIENTS   
        NOOBS
        N;
  WHERE AGE > 64;
RUN;

Subsetting observations in PROC SQL

Observations can also be subset in PROC SQL with a WHERE clause. Essentially a WHERE clause can be specified with or without a CREATE TABLE statement. When used with a CREATE TABLE statement a new table subset is created which can be used for future use. As presented earlier, a WHERE clause first evaluates whether the specified condition is true before an observation is read into the Program Data Vector (PDV) for processing. In the next example PROC SQL is used to subset patients 65 years or older with a WHERE clause and are written to the SENIORS table.

PROC SQL;
  CREATE TABLE SENIORS AS   
    SELECT *
      FROM PATIENTS
        WHERE AGE > 64;
QUIT;

About the Author
If you would like more information or have any questions about this tip, please contact Kirk Lafler, Software Intelligence Corporation at KirkLafler@cs.com. Kirk's new book, Power SAS: A Survival Guide is published by Apress and provides SAS users with the largest and most comprehensive collection of SAS tips and techniques ever offered. Power SAS can be purchased on Amazon.com and BarnesandNoble.com.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Operating System and Release Information