How to Read Data in Sas Enterprice From Excel
In this article, we will talk over 3 methods to import an Excel file into SAS.
You lot tin import an Excel file with the PROC IMPORT process, the LIBNAME statement, or the Import Data wizard in SAS University Edition. Information technology is not possible to import an Excel with a SAS Data Footstep.
Sample Data
In the examples in this article, we will import the file "shoes.xlsx". This file has 395 rows (excluding the header) and vii columns.
Below we show the get-go ten rows.
The file in a higher place is based on the SHOES dataset from the SASHELP library. To follow the examples in this commodity, you lot could consign this dataset as an Excel file starting time and save it as "shoes.xlsx".
Import an Excel File into SAS with PROC IMPORT
The easiest method to import an Excel File into SAS is with PROC IMPORT. The PROC IMPORT process provides yous great flexibility. For instance, you tin specify the canvas you want to import and start the import from the 2nd row.
How to Import an Excel File into SAS with PROC IMPORT
These are the steps to import an Excel File in SAS:
one. Define the location, file name, and file extension
The first argument of the PROC IMPORT process is the FILE=-argument. This argument specifies the location, file proper name, and file extension of the Excel file. This information must be enclosed in double-quotes.
2. Specify the SAS output dataset
The second statement of the PROC IMPORT procedure is the OUT=-argument. This argument identifies the proper noun of the SAS output dataset.
3. Specify the type of information to import
The tertiary statement of the PROC IMPORT procedure is the DBMS=-statement. DBMS stands for Database Management Systems and specifies the file type of data to import. For case, CSV, XLSX, or TXT.
The code below shows how to import the shoes.xlsx file and create the SAS dataset work.shoes with PROC IMPORT.
proc import file="/folders/myfolders/import/shoes.xlsx" out=work.shoes dbms=xlsx; run; proc print data=piece of work.shoes (obs=half dozen ) noobs; run; If you lot run the code higher up, SAS writes a message to the log indicating how many rows and columns were imported.
The prototype below shows the data that SAS imported.
How to Override an Existing Data Set up
Past default, the PROC IMPORT procedure doesn't overwrite existing datasets. For example, if you re-run the code above, SAS writes a note to the log and cancels the import.
proc import file="/folders/myfolders/import/shoes.xlsx" out=work.shoes dbms=xlsx; run; 
So, how can you overwrite an existing SAS dataset while reading an Excel file? You can add the Supplant option to the PROC IMPORT procedure to overwrite a SAS dataset.
The Supersede pick must be placed after the DBMS=-argument.
proc import file="/folders/myfolders/import/shoes.xlsx" out=piece of work.shoes dbms=xlsx replace; run; How to Import an Excel File without Header
The PROC IMPORT procedure expects that the first row of your file contains a header. But, how exercise you import an Excel file into SAS without a header?
The file shoes_no_header.xlsx contains the information of the SHOES dataset from the SASHELP library, but without a header.
If y'all apply the standard code to import an Excel file, SAS converts the first row into the column names of the new dataset.
proc import file="/folders/myfolders/import/shoes_no_header.xlsx" out=work.shoes dbms=xlsx replace; run; proc print data=piece of work.shoes (obs=6 ) noobs; run; 
To import an Excel file without a header, you need the GETNAMES=-choice. This pick lets the PROC IMPORT procedure know that the CSV file has column names or not.
By default, the value of the GETNAMES=-pick is Yes. However, if the import file doesn't have a header, you need to use GETNAMES=NO.
The code beneath shows how to utilise the GETNAMES=-option.
proc import file="/folders/myfolders/import/shoes_no_header.xlsx" out=piece of work.shoes dbms=xlsx supercede; getnames=no; run; proc impress information=work.shoes (obs=6 ) noobs; run; If you use the GETNAMES=NO choice, and so SAS sets the variables names to A, B, C, etc. Moreover, the beginning row of the Excel file is the beginning row of the data set.
The image below shows the result of importing an Excel file with the GETNAMES=NO options.
After importing the file, you can change the column names with the RENAME=-option.
Do you know? How to Rename Variables in SAS
How to Ascertain the Start Row of an Excel File
Commonly, the data of an Excel file starts on the first row. But, how practice you import an Excel file with data not starting at the commencement row?
For example, the shoes_startrow.xlsx file contains the data of the SHOES dataset from the SASHELP library. However, in dissimilarity to previous examples, the information starts in the fourth row.
If you would run the standard code of the PROC IMPORT procedure, SAS creates a dataset where the beginning rows are completely blank.
proc import file="/folders/myfolders/import/shoes_startrow.xlsx" out=work.shoes dbms=xlsx replace; run; proc print data=work.shoes (obs=half-dozen ) noobs; run; 
You could use a filter to remove the bare rows. Obviously, this is not an optimal solution. So, how do you select a starting row when you import an Excel file?
You lot can employ the DATAROW=-option to start the import of an Excel file from a specified row. If you utilise this option, the GETNAMES=-option won't work. The GETNAMES=-option just works if the variable names are in the starting time row.
In the case below, we apply the DATAROW=-selection to read an Excel file where the data starts at the fourth row.
proc import file="/folders/myfolders/import/shoes_startrow.xlsx" out=work.shoes dbms=xlsx replace; datarow=four; run; proc print information=work.shoes (obs=6 ) noobs; run; 
Subsequently importing the file, you need the RENAME=-pick to change the column names.
And so, you can use the DATAROW=-selection to define the get-go row with data in your Excel file. However, a similar option to define the first column with data doesn't exist.
If your Excel file has empty columns, y'all can notwithstanding import it and remove the empty columns subsequently.
How to Import a Range of an Excel File
Commonly, when you read an Excel file into SAS, you lot want the unabridged sheet. However, it might happen that you are interested in just a selection of the data. So, how to import a specific range of data into SAS?
Yous read a specific range from an Excel file into SAS with the RANGE=-selection of the PROC IMPORT process. This selection specifies the Excel sheet and the cells that SAS will import. The range must exist written between (double) quotes.
For example, we would like to import the range of cells highlighted in blue from the sheet "Sheet1".
We can use the RANGE=-option to specify the range we want to import into SAS. To read only the blue cells, we apply RANGE="Sheet1$C4:I15″.
proc import file="/folders/myfolders/import/shoes_range" out=work.shoes dbms=xlsx replace; range="Sheet1$C4:I15"; run; 
When y'all employ the RANGE=-choice to import a selection of an Excel file, SAS assumes that the first row contains the column names.
How to Import a Specific Sheet of an Excel File
An Excel file tin can incorporate ane or more sheets. By default, SAS imports the commencement canvass. All the same, how to import a specific sheet from an Excel file into SAS?
The Canvass=-pick of the PROC IMPORT procedure lets you specify the canvas yous want to import. You must write the name of the sheet betwixt (double) quotes. With the SHEET=-selection you can besides import sheets with spaces in their names.
In the example beneath, our Excel file has three sheets. Nosotros desire to import the canvass "Sheet2".
The following SAS code shows an instance of how to import a specific canvass. To import "Sheet2", we use Canvass="Sheet2″.
proc import file="/folders/myfolders/import/shoes_sheet" out=work.shoes dbms=xlsx replace; sail="Sheet2"; run; proc print data=work.shoes (obs=half-dozen ) noobs; run; If y'all want to import all the sheets of an Excel file, you lot can use the LIBNAME statement.
Import an Excel File into SAS with a LIBNAME Statement
The PROC IMPORT procedure is a convenient way to import an Excel file. All the same, you can also apply the LIBNAME statement.
In this section, we demonstrate how to read Excel data with a LIBNAME argument into SAS.
How to Import an Excel File into SAS with a LIBNAME Argument
With the LIBNAME statement you tin can import Excel files. The statement starts with the LIBNAME keyword, followed by the library name, the XLSX keyword, and the location of the Excel file.
The proper noun of the library can be up to 8 characters long and must start with an alphabetical character or an underscore.
The XLSX keyword lets SAS know to care for the file as an Excel file. Instead of XLSX, you can also apply the Excel keyword.
The location of the file contains both the path and the proper noun of the file. You write them between (double) quotes.
libname myfile XLSX "/folders/myfolders/import/shoes.xlsx"; If y'all run the code above, SAS will create the library "myfile" and write a bulletin to the log.
The image below shows the contents of the library.
How to Import Multiple Excel Sheets into SAS
In a previous department we showed how to import a specific canvass from an Excel file. This method works fine, just requires a lot of code if your file as many sheets. So, how to import all sheet from an Excel file into SAS?
The LIBNAME statement imports all sheets of an Excel file into SAS. It creates a library where each sheets is saved as a SAS dataset. The statement commencement with the LIBNAME keyword, followed by the name of the library, the XLSX keyword, and the location of the Excel file.
Hence, the LIBNAME statement provides a convenient and efficient manner to import at once all sheets of an Excel file. Yet, it lacks the flexibility of the PROC IMPORT procedure to define the header, the first row with data, or the range you desire to import.
Below nosotros demonstrate how to import all sheets from the "shoes_sheet.xslx" file.
libname myfile XLSX "/folders/myfolders/import/shoes_sheet.xlsx"; If y'all run the code higher up (and refresh the Libraries), you volition see that SAS created the library "myfile" with one dataset for each Excel canvas.
One time imported all the sheets, you tin use the two-level proper noun to access each dataset. For example, below we create a study of the start vi rows of Sheet1.
proc print information=myfile.sheet1 (obs=6 ) noobs; run; Note that, you can use the LIBNAME argument also to export a SAS dataset to Excel.
Import an Excel File into SAS Academy Edition
So far, we've shown how to import Excel files with PROC IMPORT and the LIBNAME statement. However, if you piece of work with SAS University Edition (or SAS Studio), you can read Excel data without writing lawmaking.
These are the steps to import an Excel file with the Import Data wizard of SAS University Edition
- Open the Import Data wizard.
You lot tin detect the Import Data sorcerer nether the Server Files and Folders section. Click on New to open the Import Information wizard.
- Select the Excel file.
The second footstep is to select the Excel file y'all desire to import. Y'all can either drag-and-drop the file, or browse in your folders.
- Specify the Sheet you want to import
By default, SAS imports the first sheet. However, you tin can specify another sheet if needed
- Specify the proper noun of the output dataset
You tin can specify where SAS saves the imported data, as well as the name of the dataset. SAS automatically overwrites existing datasets.
- Specify the header
The last option is to specify if the Excel file contains a header. If your file doesn't comprise a header, you lot need to uncheck the option "Generate SAS variable names".
- Run the Import Data magician
Import the Excel file past running the Import Information sorcerer.
Although, the Import Data magician is user-friendly to import data without writining code, information technology has besides its drawbacks. For example, you lot tin can't import a range of information, or get-go reading data from a specified row.
Import an Excel File into SAS with a DATA Step
In this article, nosotros have demonstrated iii methods to import an Excel file into SAS. Nevertheless, a common question is how to import an Excel file with a Information Pace?
In dissimilarity to a CSV file, you can't import an Excel file with a SAS data step. To import an Excel file into SAS you lot need to use the PROC IMPORT procedure or the LIBNAME statement.
How to Read Data in Sas Enterprice From Excel
Source: https://sasexamplecode.com/3-easy-ways-to-import-an-excel-file-into-sas/
0 Response to "How to Read Data in Sas Enterprice From Excel"
Post a Comment