Reading an Excel file with .xls extension into SAS
Suppose that you have an Excel spreadsheet called auto.xls. The data
for this spreadsheet are shown below.
MAKE MPG WEIGHT PRICE AMC Concord 22 2930 4099 AMC Pacer 17 3350 4749 AMC Spirit 22 2640 3799 Buick Century 20 3250 4816 Buick Electra 15 4080 7827
Using the Import Wizard is an easy way to import data into SAS. The Import
Wizard can be found on the drop down file menu. Although the Import
Wizard is easy it can be time consuming if used repeatedly. The very last
screen of the Import Wizard gives you the option to save the statements SAS uses
to import the data so that they can be used again. The following is an example
that uses common options.
proc import out = WORK.auto1 datafile= "C:auto.xls"
dbms=xls replace;
sheet="auto1";
getnames=yes;
run;
- The out= option in the proc import tells SAS what the name
should be for the newly-created SAS data and where to store the
data set once it is imported. In this example, we call the data set
auto1 and it will be stored in the WORK directory. - Next the datafile= option tells SAS where to find the file we
want to import. - The dbms= option is used to identify the type of file being
imported. - The replace option will allow SAS to overwrite an existing data
file with the same name. - To specify which sheet SAS should import use the sheet="sheetname"
statement. The default is for SAS to read the first sheet. - The getnames=yes is the default setting and SAS will
automatically use the first row of data as variable names. If the first row
of your sheet does not contain variable names use the getnames=no.
Writing .xls Excel files out from SAS
It is very easy to write out an Excel file using proc export in SAS.
Here is a sample program that writes out auto1 data to an Excel file called mydata.xls
into the directory "c:dissertation".
proc export data=auto1 outfile='c:dissertationmydata.xls' dbms = xls replace; run;
modified on October 12, 2011