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:
data bmw; set sashelp.cars; where make = 'BMW'; run;
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.
libname cars xlsx '/home/kisumsam/proc_import/cars.xlsx';
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 Excel file (i.e., CARS.xlsx) does not have to exist for the library to be connected to this file.
In our example, the CARS.xlsx does not exist and the CARS library is empty.
However, when we create a data set in this library, the Excel file will be automatically created.
You will see an example shortly.
The CARS library is now created. We will now create the BMW data set in the CARS library:
libname cars xlsx '/home/kisumsam/proc_import/cars.xlsx'; data cars.bmw; set sashelp.cars; where make = 'BMW'; run;
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 in the proc_import directory:
You simply need to right click the file and click the "Download" link to download the file.
Download and open the Excel file on your PC. 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.
libname cars clear;
The Clear argument disassociates the Cars library reference in SAS.
The Cars 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.
** Remember to change the path in the FILENAME statement **;
libname City xlsx '/home/your_user_name/proc_import/excel_practice_02.xlsx';
where CrimeCode = '4E';