topic Re: Proc Import not importing all observations in SAS Procedures

Proc Import not importing all observations

mahler_ji — Tue, 27 Jan 2015 22:16:30 GMT

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

Re: Proc Import not importing all observations

art297 — Tue, 27 Jan 2015 22:22:41 GMT

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

Re: Proc Import not importing all observations

ballardw — Tue, 27 Jan 2015 22:26:50 GMT

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.

Re: Proc Import not importing all observations

mahler_ji — Tue, 27 Jan 2015 22:33:03 GMT

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!