SAS - proc import with excel file, dbms not valid

Question

I have a .xls excel file (Microsoft Excel 97-2003 Worksheet) which I want to import to SAS. So I used:

proc import datafile = "C:\Users\***\***\data.xls" 
        out = data dbms = EXCEL;
run;

However I get a following error:

ERROR: DBMS type EXCEL not valid for import.

I have tried different DBMS (I do not really get the difference in many cases, but just took a guess with EXCEL97, EXCEL4, EXCEL5, XLS). None of these worked. Do you have any idea how to deal with that?

The few first columns look like below:

id      status      start       end         duration   browser    browserversion   country              Age
51      complete    03/08/2016  03/08/2016  0:21:57    Chrome     65.0             1                    51
133     complete    03/08/2016  03/08/2016  0:10:07    Chrome     58.0             1                    18
1002    complete    03/08/2016  03/08/2016  0:17:57    Chrome     58.0             1                    40

How can I check that? If it is a kind of application (like sas enterprise guide) then probabli not — jakes
– jakes, Commented Mar 26, 2018 at 13:48

Tom · Accepted Answer · 2018-03-26 14:12:30Z

5

You need to have a licence. Run PROC SETINIT and see if you have this product licensed.

---SAS/ACCESS Interface to PC Files

If not then save the excel file as a CSV file and read it using a data step. It is really easy.

data myfile ;
  infile "C:\Users\***\***\myfile.csv" dsd firstobs=2 truncover ;
  length varfirst 8 var2 $30 .... varlast $5 ;
  input varfirst -- varlast ;
run;

Add an INFORMAT and FORMAT statement for any variables that need them, like dates. Normal character or numeric variables do NOT need either an INFORMAT or a FORMAT for SAS to be able to read or display them properly.

answered Mar 26, 2018 at 14:12

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

jakes Over a year ago

That is helpful. However, I have around 200 columns so specifying their length one after one is rather tedious. I have tried to use proc import with csv file, but it reads all in one column and one row with data separated by semicolon. In Csv file I have data in separate column. And here is what I used proc import datafile = "C:\Users\***\***\my_file.csv" out = data dbms = csv replace; run; Is there a way to fix it?

Tom Over a year ago

If the file is well structured then 200 columns is not really a problem. For example you might just have an ID variable and 199 numeric variables. length id $10 var1-var199 8;

Tom Over a year ago

You need to tell SAS what delimiter to use. Sounds like you want to use dbms=dlm and add the delimiter=';'; statement.

jakes Over a year ago

proc import datafile = "C:\Users\***\myfile.csv" out = data dbms = dlm replace; delimiter = ';' ; run; didn't work. Also my file contains columns with different length (ex. there are ids of length 2 or 3 or even 4), so I don't think data step will do here. Really appreciate the effort though!

Tom Over a year ago

If you want help with syntax to read your file post a few lines of the text. You probably don't need to show all 200 columns. Either edit the question or start a new one. Defining the lengths for 200 variables would have taken a fraction of the time you have spent trying to get SAS to guess at what is in the file. You can just copy the headers from your data file and edit it into a length statement. Numeric variables are always length 8 and for character just make them longer than you expect to need. You can change it later and re-run.

|

Kiran · Accepted Answer · 2018-03-26 12:57:45Z

2

Please Try Dbms = xlsx or dbms = xls. For older Microsoft office xls engine works.

answered Mar 26, 2018 at 12:57

Kiran

3,3153 gold badges16 silver badges24 bronze badges

2 Comments

jakes Over a year ago

As I mentioned earlier I had tried XLS. Now I gave it a go with XLSX. Neither of them works.

DeadlyChambers Over a year ago

The xlsx was it for me. Thanks. Much appreciated, if I am using SAS University, will that capability at some point?

Collectives™ on Stack Overflow

SAS - proc import with excel file, dbms not valid

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related