Variables format in SAS proc import

Question

When importing an excel file into SAS, I find that the import is not being done properly due to wrong variables'format.

The table trying to import look like this:

ID  Barcode
1   56798274
2   56890263
3   60998217
4   SKU89731
...

The code I am using is the following:

PROC IMPORT OUT= WORK.test
        DATAFILE= "D:\Barcode.xlsx" 
        DBMS=EXCEL REPLACE;
 RANGE="Sheet1$"; 
 GETNAMES=YES;
 MIXED=NO;
 SCANTEXT=YES;
 USEDATE=YES;
 SCANTIME=YES;
 RUN;

What happens is that column "Barcode" has best12. format and therefore cases as ID=4 get a missing value ('.') because they originally had both characters and numbers.

Since it is not possible to change the format of a variable in a proc step how can I import the file correctly, and only using SAS editor?

EDIT:

Another option that does half the work and it might give some inspiration is dynamically changing the format of the variable by importing through a data step:

libname excelxls Excel "D:\Barcode.xlsx";

data want;
set excelxls.'Sheet1$'n (DBSASTYPE=(Barcode='CHAR(255)'));
run;

With the above code I force SAS to import the variable in the format I want (char), but still missings are generated for values as in ID=4.

Bendy Bendy · Accepted Answer · 2015-06-02T12:17:35

I think your problem is that you have mixed=NO. Change this to mixed=YES and SAS will check a sample of the observations to see if there are any non-numeric characters in the variables - if it finds one then it will specify the variable as character.

Take a look at the link here for more information:

Variables format in SAS proc import

2 Answers