This example uses data similar to the scenario used for the raw data in the previous section. The data shows the readings from exercise stress tests that have been performed on patients at a health clinic.

The stress test data is located in an Excel workbook named exercise.xlsx (shown below), which is stored in the location c:users.

In the sample worksheet above, the date column is defined in Excel as dates. If you right-click the cells and select Format Cells, the cells have a category of Date. SAS reads this data just as it is stored in Excel. If the date had been stored as text in Excel, then SAS would have read it as a character string.

To read in this workbook, create a libref to point to the workbook's location:

libname results XLSX 'c:certdataexercise.xlsx';

The LIBNAME statement creates the libref Results, which points to the Excel workbook exercise.xlsx. The workbook contains two worksheets, Tests and Adv, which are now available in the new SAS library (Results) as data sets.

Use the SET statement to indicate which worksheet in the Excel file you want to read.

data work.stress;
   set results.'ActivityLevels'n;
run;

In this example, the DATA statement tells SAS to name the new data set, Stress, and store it in the temporary library Work. The SET statement in the DATA step specifies the libref (the reference to the Excel file) and the worksheet name as the input data.

You can use several statements in the DATA step to subset your data as needed. Here, the WHERE statement is used with a variable to include only those participants whose activity level is HIGH.

data work.stress;
   set results.'ActivityLevels'n;
   where ActLevel='HIGH';
run;

The figure below shows the partial output for this DATA step in table format.

The LIBNAME statement created a permanent library, Results, which is the libref for the workbook file and its location. The new library contains two SAS data sets, which access the data from the Excel worksheets.

The Excel worksheet names have the special character ($) at the end. All Excel worksheets are designated this way. But remember, special characters such as these are not allowed in SAS data set names by default. So, in order for SAS to allow this character to be included in the data set name, you must assign a name literal to the data set name. A SAS name literal is a name token that is expressed as a string within quotation marks, followed by the uppercase or lowercase letter n. The name literal tells SAS to allow the special character ($) in the data set name.

A named range is a range of cells within a worksheet that you define in Excel and assign a name to. In the example below, the worksheet contains a named range, tests_week_1, which SAS recognizes as a data set.

The named range, tests_week_1, and its parent worksheet, tests, appear in the SAS windowing environment as separate data sets, except that the data set that was created from the named range has no dollar sign ($) appended to its name.

For more information about named ranges, see your Microsoft Excel documentation.