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:UsersStudent1cert.

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 certxl XLSX 'C:UsersStudent1certexercise.xlsx';

The SAS/ACCESS LIBNAME statement creates the libref Certxl, 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 certxl.ActivityLevels;
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 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 certxl.ActivityLevels;
   where ActLevel='HIGH';
run;

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

The SAS/ACCESS LIBNAME statement created a permanent library, Certxl, 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.

Name literals are required with the XLSX engine only when the worksheet name contains a special character or spaces. By default, SAS does not allow special characters in SAS data set names. 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.

The following example illustrates reading an Excel worksheet using a name literal. Specify the name of the worksheet in quotation marks with an n following the name. This syntax tells SAS that there are special characters or spaces in the data set name.

libname certxl xlsx 'C:UsersStudent1certstock.xlsx';
data work.bstock;
  set certxl.'boots stock'n;
run;