Converting SPSS Data to Excel Data and Then to SAS

Converting SPSS Data to Excel Data and Then to SAS

I have in an SPSS .sav file, Howell.sav, the data from the appendix of our statistics text. I want to bring these data into SAS. SAS will not read an SPSS data file, but you can convert it to another type of file that SAS can read. There are several choices for the intermediary type of file. Here I use and Excel worksheet as the intermediary file.

First, I open Howell.sav in SPSS 11.0. I then click File, Save As. In the dialog box, shown below, I select type = Excel (*.xls) and specify file name Howell.xls. I click Save and the Excel file is written.

Here is what is written to the SPSS output window:

Data written to E:\SPSS\Howell.xls.

9 variables and 88 cases written to range: SPSS.

Variable: ADDSC Type: Number Width: 5 Dec: 0

Variable: GENDER Type: Number Width: 6 Dec: 0

Variable: REPEAT Type: Number Width: 6 Dec: 0

Variable: IQ Type: Number Width: 3 Dec: 0

Variable: ENGL Type: Number Width: 4 Dec: 0

Variable: ENGG Type: Number Width: 4 Dec: 0

Variable: GPA Type: Number Width: 4 Dec: 2

Variable: SOCPROB Type: Number Width: 7 Dec: 0

Variable: DROPOUT Type: Number Width: 8 Dec: 0

When I open Howell.xls with Excel I get a warning, shown below.

But not to worry, I just click OK and the spreadsheet looks fine.

The spreadsheet looks in good order, but it is not yet in a format that SAS will accept. Apparently it is in a very old Excel format, and we need to convert into a current format. Accordingly, I click File, Save As, and notice that the spreadsheet is in Excel 2.1 format. I change the format to Microsoft Excel Workbook (*.xls), and Save it, replacing the existing file, as shown below:

Now I close Excel and open SAS. I click File, Import Data. The initial screen already has the defaults I want, so I just click Next:

I Browse to the input Excel file, as shown below:

and click Next. On the next window, I leave the library at WORK and I type in any member name that starts with a letter, as shown below:

I click Next. On the final window, I elect (optionally) to have SAS create a little program file that contains all the PROC IMPORT statements needed to import this Excel file without going through the Import Wizard. After browsing to the desired directory, I enter the name of the file into which to write those statements and click Finish.

When I look at the SASLOG, I see the message

NOTE: WORK.HOWELL was successfully created.

Now I can start right in with program statements, using the variable names that were in the spreadsheet. For example, I command PROC MEANS; RUN; and get the following output:

The MEANS Procedure

Variable Label N Mean Std Dev Minimum Maximum

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

ADDSC ADDSC 88 52.6022727 12.4222083 26.0000000 85.0000000

GENDER GENDER 88 1.3750000 0.4868973 1.0000000 2.0000000

REPEAT REPEAT 88 0.1363636 0.3451409 0 1.0000000

IQ IQ 88 100.2613636 12.9849553 75.0000000 137.0000000

ENGL ENGL 88 1.9545455 0.5232325 1.0000000 3.0000000

ENGG ENGG 88 2.6590909 0.9454837 0 4.0000000

GPA GPA 88 2.4562500 0.8614307 0.6700000 4.0000000

SOCPROB SOCPROB 88 0.1136364 0.3191878 0 1.0000000

DROPOUT DROPOUT 88 0.1136364 0.3191878 0 1.0000000

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

Remember that I asked SAS to create a file that contained the PROC IMPORT statements needed to read the Excel file directly, without having to go through the import wizard again. Here is what that file looks like:

PROCIMPORT OUT= WORK.Howell

DATAFILE= "E:\SPSS\Howell.xls"

DBMS=EXCEL2000 REPLACE;

GETNAMES=YES;

RUN;

Running these statements will bring the data back into SAS from the Excel file.

Return to the StatHelp page.