Department of Statistics

Import Wizard, SAS version 6.12

My initial experiences with the import wizard were very promising. It was very simple to use. I was able to create SAS data sets from EXCEL files with ease. However, when presented with a data set that had variables whose values were a miscellaneous mix of both numbers and characters, I ran into problems -- I lost data!

It appears that the IMPORT WIZARD assigns variables a type, numeric or character, based on the most common type in each column; this is the default method. Alternatively, SAS will assign the variable a type, numeric or character, based on the type of the first data value for each variable . To request the alternative method, use the following PROC ACCESS statement:   scantype=no;

Unfortunately, neither of these methods alone were adequate for reading my data set. I needed the variable type to be character, so that both numbers and characters would be valid data. I needed complete data preservation.

 I discovered that variable type can be set explicitly with the type statement. The syntax is type column-identifier1=c|n   column-identifier2=c|n;   I also learned that if the first observation is a blank and scantype=no is specified, the column is defined as a character variable.

Lastly, the statement    mixed=yes   is needed to read both numbers and characters in a single column.

The exact method I used to read my data set follows.

• I  added one blank line to the top of my EXCEL file.
• I  used the IMPORT WIZARD to read the data ( all default settings).
• In the program editor window, I issued the recall command twice to recall the
  code the IMPORT WIZARD had submitted.
• I added the statement          mixed=yes;
• I change the statement    scantype=yes;    to    scantype=no;
• I resubmitted the code.
• I printed and checked the data. Then I deleted the first observation. -- This observation was the result of the blank line I had added to the excel file; it contained no data.
• There are also environment variables that can be set to change the default action of the IMPORT WIZARD. They are ss_mixed and ss_scan  .  For more information look at the SAS/ACCESS For PC File Formats Reference Guide.

Here is a copy of the final code I ran:

Most of this program was written by the import wizard. The parts I changed are highlighted.

PROC ACCESS DBMS=EXCEL;
CREATE WORK._IMEX_.ACCESS;
PATH='C:\JOYSAS\test3.xls';
GETNAMES YES;
SCANTYPE=NO;
MIXED=YES;
CREATE WORK._IMEX_.VIEW;
SELECT ALL;
RUN;
DATA WORK.test3;
SET WORK._IMEX_;
RUN;
PROC DATASETS LIBRARY=WORK MEMTYPE=ACCESS NOLIST;
DELETE _IMEX_;
QUIT;
PROC DATASETS LIBRARY=WORK MEMTYPE=VIEW NOLIST;
DELETE _IMEX_;
QUIT;

proc print; run;

OBS   COL1   COL2   COL3   COL4   COL5
 1
 2     a      b      1      3      b
 3     a      1      1      a      b
 4     s      1      1      c      1