Help using Base SAS procedures

Proc Import not importing all observations

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Proc Import not importing all observations

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


Accepted Solutions
Solution
‎01-27-2015 05:22 PM
PROC Star
Posts: 7,363

Re: Proc Import not importing all observations

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

View solution in original post


All Replies
Solution
‎01-27-2015 05:22 PM
PROC Star
Posts: 7,363

Re: Proc Import not importing all observations

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

Frequent Contributor
Posts: 101

Re: Proc Import not importing all observations

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!

Super User
Posts: 10,500

Re: Proc Import not importing all observations

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 403 views
  • 0 likes
  • 3 in conversation