BookmarkSubscribeRSS Feed
amymcox10
Calcite | Level 5

I'm trying to import an Excel .xlsx file but keep running into a few problems. I didn't write this code and this is my first time using SAS so step by step instructions are the best for me. This code was written for an on computer version of SAS whereas I am using the University Edition. 

The PROC IMPORT code I'm using is as follows:

1      OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
42     ;
43     PROC IMPORT OUT= WORK.FUTURES REPLACE
44                 DATAFILE= "/folders/myfolders/Sugar Beet Steers.xlsx"
45                 DBMS=XLS REPLACE;

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

46          RANGE="Sheet1$";
47          SCANTEXT=YES;
             ________
             180
48          USEDATE=YES;
             _______
             180
49          SCANTIME=YES;
             ________
             180

ERROR 180-322: Statement is not valid or it is used out of proper order.

50     RUN;

Spreadsheet isn't from Excel V5 or later.  Please open it in Excel and Save as V5 or later

Requested Input File Is Invalid

ERROR: Import unsuccessful.  See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

   real time       0.04 seconds
   cpu time        0.05 seconds
 
51     ;
52     OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
62    

;

And the log keeps showing me this:

1      OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
42     ;
43     PROC IMPORT OUT= WORK.FUTURES REPLACE
44                 DATAFILE= "/folders/myfolders/Sugar Beet Steers.xlsx"
45                 DBMS=XLS REPLACE;

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

46          RANGE="Sheet1$";
47          SCANTEXT=YES;
             ________
             180
48          USEDATE=YES;
             _______
             180
49          SCANTIME=YES;
             ________
             180

ERROR 180-322: Statement is not valid or it is used out of proper order.

50     RUN;

Spreadsheet isn't from Excel V5 or later.  Please open it in Excel and Save as V5 or later

Requested Input File Is Invalid

ERROR: Import unsuccessful.  See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

   real time       0.04 seconds
   cpu time        0.05 seconds
 
51     quit;
52     ;
53     OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
63     ;
3 REPLIES 3
ballardw
Super User

The key is in this line:

Spreadsheet isn't from Excel V5 or later.  Please open it in Excel and Save as V5 or later

Requested Input File Is Invalid

though possibly a tad misleading XLS and XLSX are different file formats. Your code tells SAS that you want to read the input file as XLS but the name indicates it is later.

Try DBMS=EXCEL.

All the other errors are due to this.

amymcox10
Calcite | Level 5

When I changed to DBMS=EXCEL it takes care of the ERROR 180-322 but the log still says this:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 PROC IMPORT OUT= WORK.FUTURES REPLACE

44 DATAFILE= "D:/folders/myfolders/Sugar Beet Steers.xlsx"

45 DBMS=EXCEL REPLACE;

ERROR: DBMS type EXCEL not valid for import.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

  real time 0.01 seconds

  cpu time 0.01 seconds

  

46 RANGE="Sheet1$";

47 SCANTEXT=YES;

48 USEDATE=YES;

49 SCANTIME=YES;

50 RUN;

51 quit;

52 ;

53 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

63 ;

Meng_Xu
Fluorite | Level 6

If you would like to use 'DBMS=EXCEL' or 'DBMS=EXCELCS', you have to use PC File server.

 

I have the same error as yours. What I did is to comment out those options, and fortunately I still got the data I want. I'll keep searching some solutions on that error.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4654 views
  • 1 like
  • 3 in conversation