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:
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.
If the Excel file already exists, SAS will add a new workbook or overwrite the existing workbook when exporting the data using the XLSX engine.
libname City xlsx '/folders/myfolders/Proc Import Files/Excel_practice_02.xlsx';
where CrimeCode = '4E';