Proc Import [7-7]

Creating an Excel Spreadsheet using the XLSX Engine

The XLSX engine can also be used to export data to an Excel spreadsheet. 

Let's look at the code below:

The code above is a simple data step that extracts the 'BMW' cars from the SASHELP.CARS data set.

Let's suppose we want to export it to an Excel spreadsheet.

We will first create a new library that connects to an Excel spreadsheet.

The LIBNAME statement above will create a new library called CARS:

The CARS library is connected to the CARS.xlsx file.

However, the library is empty because the CARS.xlsx does not even exist.

The CARS library is now created. We will now create the BMW data set in the CARS library:

The code above saves the BMW data set in the CARS library:

The BMW data set is now created in the CARS data set:

The CARS.xlsx file is created:

The CARS.xlsx has a worksheet called BMW. It contains the data that we just exported:

Disassociating the Library Reference

You can disassociate a library reference using the CLEAR argument in the LIBNAME statement. 

Below is an example.

The CLEAR argument disassociates the CARS library reference in SAS.

The CAR library is now removed:


Exercise

Using the XLSX engine, read the data from the Crime worksheet in Excel_Practice_02.xlsx.

Filter the data set and keep only the observations with CrimeCode = '4E'.

Export the data to a new workbook called Crime_4E in the same spreadsheet.

Get Hint

Get Solution