topic Re: ERROR 180-322: Statement is not valid or it is used out of proper order. in SAS Programming

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

kegong — Thu, 16 May 2019 15:43:34 GMT

Dear experts,

I received this error 180-322 message while I was trying to run below:

PROC IMPORT OUT= WORK.FISCAL
DATAFILE= "D:\Summer 2019 Work\Summer19\Ke Summer Data"
DBMS= XLS;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

And this is the detailed error message:

1 PROC IMPORT OUT= WORK.FISCAL
2 DATAFILE= "D:\Summer 2019 Work\Summer19\Ke Summer Data"
3 DBMS= XLS;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
4 RANGE="Sheet1$";
NOTE: The previous statement has been deleted.
5 GETNAMES=YES;
6 MIXED=NO;
7 SCANTEXT=YES;
--------
180
8 USEDATE=YES;
-------
180
9 SCANTIME=YES;
--------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

I'm not sure what happened. Could someone please help me out? Thanks very much!

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

stechafle — Thu, 16 May 2019 16:15:49 GMT

Maybe try DBMS=EXCEL instead? I couldn't find any documentation on DBMS=XLS that had all of the options you listed. See https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=acpcref&docsetTarget=n0msy4hy1so0ren1acm90iijxn8j.htm&locale=en for more documentation.

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

kegong — Thu, 16 May 2019 21:24:25 GMT

@stechafle Thanks a lot for your reply! Below is what I originally used:

PROC IMPORT OUT= WORK.FISCAL
DATAFILE= "D:\Summer 2019 Work\Summer19\Ke Summer Data"
DBMS=EXCEL REPLACE;

But then I got another error message for running this.

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed. See log for details

Seems like this error happens when importing 32-bit excel file to a 64-bit SAS.But my Office 365 is already 64-bit. So I'm totaly confused...

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

ballardw — Thu, 16 May 2019 22:32:38 GMT

How about showing the actual entire name of the data file???

DATAFILE= "D:\Summer 2019 Work\Summer19\Ke Summer Data" is sort of incomplete from many purposes.

You may also have to check to see if someone "lied" to Excel. You will find people that create files as HTML, XML or even CSV but name the file with XLS or extension so Excel will be the default program to open the file. Any of those will likely fail to import.

Or try doing a FILE SAVE AS from Excel to a CSV format and import that.

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

kegong — Fri, 17 May 2019 00:40:21 GMT

@ballardw Thank you very much but my problem remains... I double checked the file, the data file property shows that it's an xls file. I even tried convert it into a csv and an xml file. Neither one works when I tried to import them into SAS...

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

ChrisNZ — Fri, 17 May 2019 02:30:18 GMT

Why don't you specify the proper file name?

DATAFILE= "D:\Summer 2019 Work\Summer19\Ke Summer Data.xls"

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

kegong — Fri, 17 May 2019 02:55:34 GMT

@ChrisNZ Thanks a lot for your help. I added xls to the file name but the problem remains. But it worked when I saved the xls file as a csv file and deleted below rows:

RANGE="Sheet1$";

MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;

So now I'm using this and it works out well:

PROC IMPORT OUT= WORK.FISCAL
DATAFILE= "D:\OD - Copy\Research Projects\Dr. Johnson\CEO effect\Summer 2019 Work\Summer19\Ke_Summer_Data.csv"
DBMS=CSV REPLACE; GETNAMES=YES;
RUN;

But I'm not sure whether deleting the setting of date format etc damaged the dataset...

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

ChrisNZ — Fri, 17 May 2019 03:45:21 GMT

Can't comment further without seeing more code, but the messages you were seeing relate to invalid/incompatible values for the different options chosen (DBMS= SCANTEXT= USEDATE= SCANTIME= etc). Not all engines use all options.

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

kegong — Fri, 17 May 2019 03:50:47 GMT

@ChrisNZ Thanks again for clarifying. I think the data imported to SAS is good. So I'll go with the reduced code. Have a nice weekend!

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

ballardw — Fri, 17 May 2019 16:51:38 GMT

@kegong wrote:

@ChrisNZ Thanks a lot for your help. I added xls to the file name but the problem remains. But it worked when I saved the xls file as a csv file and deleted below rows:

RANGE="Sheet1$";

MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;

So now I'm using this and it works out well:

PROC IMPORT OUT= WORK.FISCAL
DATAFILE= "D:\OD - Copy\Research Projects\Dr. Johnson\CEO effect\Summer 2019 Work\Summer19\Ke_Summer_Data.csv"
DBMS=CSV REPLACE; GETNAMES=YES;
RUN;

But I'm not sure whether deleting the setting of date format etc damaged the dataset...

I suggest also using GUESSINGROWS=MAX; This option, not available for XLS, XLSX and many database formats, works with delimited text such as CSV to examine the entire dataset before guessing as the variable type and length. SCANTEXT is somewhat similar in purpose.

But do look at the data step code generated to ensure the variables are read as needed.

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

kegong — Fri, 17 May 2019 16:55:13 GMT

@ballardw Thank you so much. It works pretty well.