SAS Import XLSX- Importing Excel Files (.xlsx)
sas import xlsx, proc import sas, proc import csv, proc import xlsx, sas import excel, proc import sas excel, sas import xlsx, proc import sas excel, proc import, proc import example, sas import xlsx, sas proc import csv, proc import excel file, sas import excel file
SAS Import XLSX
Proc import is one way to import data sets into SAS. It is primarily used to import .xlsx files.In SAS Base, you can use proc import to import any delimited files. Nevertheless, it is important to understand the limitations of proc import, and when it makes more sense to utilize the data step (if possible).
DATAFILE=file location (don’t forget double quotation marks for csv files)
GETNAMES=no; (or yes) (Specifies whether the IMPORT procedure generates SAS variable names from the data values in the first record in the input file.)
REPLACE; (replaces last data set)
The Advantages and Disadvantages of Proc Import
Proc import can save you a lot of time as it utilizes clues to guess the type and format of variables. It does this by scanning only a small number of rows and uses this information to determine factors such as the type and format of a variable. So you save time by skipping the declaration of variables, declaration of their types, and their informats. Remember, when you use data step processing to import, you have to declare your variables, variable types, and often need to use the right informat.
The disadvantage of the way proc import works is that sometimes this “guessing” game leads to an inaccurate reading of data sets. For example, it might read some variable as a character, even though it is in fact numeric.
Nevertheless, there are two tips that I can give you to make sure that your data sets are read accurately when using proc import.
The first tip is to use the guessingrows option. By using this option, you can select a greater number of rows for SAS to scan before it decides the type or format of a variable in a data set. So how many rows should you select? The answer to this question will be highly dependent on how many observations you have. In most cases, selecting 1/3rd of the rows might be sufficient.
The first thing that you want to do after importing is to check the contents of the data set to ensure that it was imported properly. You can use proc contents to check the data contents.
Let’s assume that SAS has erroneously read a numeric variable as a character variable. This is where my second tip comes in to play.
proc contents data=nameofdataset;
First, I want you to go to the log. Second, I want you to replace REFILE with the location of your data set. Don’t forget the double quotation marks around the file location. Next, find the variable that was read erroneously. You will find it under input. I want you to modify the variables character informat and format to a numeric informat and format. The next step is to take the $ away in the input statement since we want to modify the variable to be a numeric variable now. Finally, you want to delete all the row numbers (on the left-hand side). When you hit run, the changes will be made.
I hope this lesson on how to use sas to import an xlsx file was useful. If you are still having trouble, please leave your question in the comments. Furthermore, you could always try the data step to import your data sets.