How to Read Excel Data in Sas

PROC IMPORT is the SAS process used to read data from excel into SAS. This tutorial covers how to import excel data to SAS with PROC IMPORT. Loading excel data to SAS is i of the about mutual job of a SAS programmer / annotator. Nearly of the raw data files are saved in MS Excel so we need to have it to SAS for further analysis.

SAS : PROC IMPORT Excel

PROC IMPORT Syntax:

one. DATAFILE= selection tells SAS where to observe the Excel file that you want to import (Consummate filename path).
For example : DATAFILE = "C:\Desktop\age.xls"

If you lot are using SAS Academy Edition
Yous demand to find shared folder which is generally available as Folders > My Folders
In SAS University edition, file location would be
DATAFILE = "/folders/myfolders/Boards.xls"

Import Excel file in Academy Edition

2.OUT= option tells SAS to create a dataset with whatever name of your pick. Past default, the imported dataset is saved on WORK library (temporary library)
Examples :
i. OUT = Age .  In this statement, PROC IMPORT uses the Work library. This implies OUT = Historic period is equivalent to OUT = Work.Age .
two. OUT = Input.Historic period. In this argument, PROC IMPORT uses the Input library (Permanent library).

iii.DBMS=choice tells SAS the type of file to read.
Examples :
i. DBMS =  XLS for Excel 97-2003 workbooks 2.DBMS =  XLSX for Excel 2007 - 2013 workbooks

iv. Replace is used to overwrite the existing SAS dataset (If whatsoever) mentioned in the OUT= pick.
5. Canvass= option is used to specify which canvass SAS would import.
Examples :
i.SHEET =  "Sheet1" - To import data from worksheet named sheet1. ii.SHEET =  "Goal" - To import information from worksheet namedGoal.

six. GETNAMES= Aye tells SAS to use the starting time row of data as variable names.

By default, PROC IMPORT uses GETNAMES= Yes. If yous type GETNAMES= NO, SAS would not read variable names from kickoff row of the sail.

7. DATAROW= option is used to specify starting row from where SAS would import the information.

For example : DATAROW =5tells SAS to get-go reading data from row number 5.

Note :

i. When GETNAMES=YES, DATAROW must exist greater than or equal to 2.
ii. When GETNAMES=NO, DATAROW must be greater than or equal to 1

8. RANGE= pick is used to specify which range SAS would import.

Examples :

i.RANGE="Sheet1$B2:D10"
This would tell SAS to import information from range B2:D10 from sheet1

ii.RANGE="Information"
This would tell SAS to import data from excel defined name range. In the example shown above, it is Information.

Importing an XLS (MS Excel 97-2003)  format file into SAS

1. DATAFILE= "C:\age.xls" tells SAS where to observe the Excel file that you desire to import.

In University Edition, DATAFILE = "/folders/myfolders/age.xls"

2. OUT= WORK.age tells SAS to create a dataset named age stored in WORK library
3. DBMS= XLS tells SAS the XLS (Excel 97-2003) format file to read.
4. REPLACE is used to overwrite the age dataset if it exists already.
5. Sheet= "Sheet1" tells SAS to import information from Sheet1.
6. GETNAMES="Aye" tells SAS to employ the first row of data as variable names.

Importing an XLSX (MS Excel 2007-2013)  format file into SAS

DBMS=XLSXtells SAS the XLSX (Excel 2007-2013) format file to read.

Important Notation -

Earlier SAS Versions earlier SAS9.2 does non back up XLSX formatted file (Excel 2007 or later files). If your XLSX file contains records fewer than 65000 rows and 255 columns, you lot can save the file in XLS format past clicking on Relieve Every bit >> Excel 97-2003 Workbook. Later y'all can import the converted XLS file into SAS.

Importing an excel file from specified row

DATAROW=fivetells SAS to start reading information from row number 5. In this case, variable (column) names would be pulled from start row but cavalcade values would be extracted from row 5.

Importing variable name from other than outset row

Suppose variable names are placed at second row in excel canvass.

PROC IMPORT DATAFILE= "E:\SAS Code Repository\Book1.xls"
DBMS=XLS
OUT= TEMP  REPLACE;
NAMEROW=2;
STARTROW=3; GETNAMES=YES;
RUN;

NAMEROW=two tells SAS to excerpt variable names from second row and STARTROW=3 is used to pull values from third row.NAMEROW merely works with XLS but not with XLSX.

Importing only specified columns from excel file

The OUT = filename followed past KEEP=statement is used to retain the desired variables. In the case shown above, we retained four variables ID,Ten,Y and Z.

In the same way, yous can employ Drib= statement to remove the variables that you lot don't want to retain.

For instance : You don't want to import 3 variables say A, B and C.

PROC IMPORT OUT= Work.desire (Drop=A B C)  DATAFILE= "C:\Desktop\File1.xls"  DBMS=XLS REPLACE;      GETNAMES=YES; RUN;

Importing only rows that take non-missing data for all the variables

In the example shown in a higher place, WHERE= statement is used to delete all the rows that have only missing data on variables x,y and z.

Importing data from excel using specified range

RANGE="Sheet1$B4:E100" tells SAS to import data from range B4:E100 from sheet1.

Importing data from excel using named range

Range Proper noun: In MS Excel, it is a name that represents a jail cell, range of cells. You can create your ain defined name.

Creating a range name is very simple. Select a cell or range of cells and Click on the Name box above Column A and Tye any name you lot want and Press Enter.

In the instance beneath, Range A1:C10 is selected and then blazon Info in the name box and Press Enter.

SAS : Using Excel Named Range

RANGE="Info"tells SAS to import data from excel using user divers named range Info.

Rename columns while Importing

The variable names can be renamed using RENAME= choice side by side to OUT= option.

PROC IMPORT DATAFILE= "E:\SAS Code Repository\Book1.xlsx"
DBMS=XLSX
OUT= TEMP (RENAME=(Score=TotalScore))  Supplant;
GETNAMES=YES;
RUN;

Importing an excel file from website into SAS

filename test temp;
proc http
url="https://www2.census.gov/acs2005/GEORES.xls"
method="Become"
out=examination;
run;

proc import file=exam
out=readin supplant
dbms=xls ;
NAMEROW=three;
STARTROW=4;
run;

SAS provides a method for extracting information from web pages by procedure named PROC HTTP. This method makes it piece of cake to read data from web pages. In this case, variable name starts from row number 3 in datafile and data values beginning from row4 onwards. To import CSV file from website, you lot just demand to change the DBMS=XLS to DBMS=CSV.

Almost Writer:

Deepanshu founded ListenData with a simple objective - Make analytics easy to sympathize and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains similar Banking, Insurance, Private Equity, Telecom and Human Resource.

schneidermobleclat.blogspot.com

Source: https://www.listendata.com/2013/09/importing-excel-data-into-sas.html

Share this post