Proc Import not importing all observations

[mahler_ji]

Hello All,

I am trying to import the attached .xls file, and not all of the observations are being imported.  Just the first 65536 observations.  I checked, and it is not missing any observations from 1 to 65536.  It is importing the columns correctly, and correctly guessing the delimiter, which is a space, in this instance.

I am using the following code:

proc import datafile = '/home/mit/mahler/Abnormal_Weather/Data/StateData.xls'  dbms=xls  out=dat.StatesFinal  replace;

  getnames = no;

run;

As you can see, from the attached file, the original data has 96191 observations.

I am sure this is an easy fix, but I can't figure it out.

Thanks,

John

[art297 · Accepted solution]

An xls file can only contain up to 65536 records. An xlsx file can contain more.

[mahler_ji]

That fixed it!  Thanks so much!

Just saved it as a .xlsx file and used the following code:

rsubmit;

proc import datafile = '/home/mit/mahler/Abnormal_Weather/Data/StateData.xlsx'  dbms=xlsx  out=dat.StatesFinal  replace;

  getnames = no;

run;

Thanks!

[ballardw]

Since it looks like you may be using the University edition and I don't I can't test..

XLS would not pay any attention to delimiters as XLS files don't have such if a native Excel file.

If it is delimited text, say so.

I would be tempted to export using Excel or other spreadsheet program to a CSV and import that.