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.

Brian16 — Mon, 21 Feb 2022 22:46:12 GMT

Hi everyone,

I am a new SAS use and get ERROR 180-322: Statement is not valid or it is used out of proper order.

Could you please help me!

Here is the log

140 proc import datafile = "K:\Variant Taskforce\Variant Codes\Variant Types and
140! Snomed_V20220126.xlsx"
141 out = work.Variants
142 dbms= XLSX replace;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
143 RANGE = "Most Recent$";
NOTE: The previous statement has been deleted.
144 GETNAMES = YES;
145 MIXED = YES;
-----
180
146 SCANTEXT = YES;
--------
180
147 USEDATE = YES;
-------
180
148 SCANTIME = YES;
--------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

149 run;

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

and here is the code

proc import datafile = "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx"
out = work.Variants
dbms= XLSX replace;
RANGE = "Most Recent$";
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
run;

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

Kurt_Bremser — Mon, 21 Feb 2022 22:56:50 GMT

MIXED, SCANTEXT, USEDATE and SCANTIME are not supported for DBMS=XLSX.

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

Brian16 — Mon, 21 Feb 2022 23:00:10 GMT

It was EXCEL instead of XLSX. I worked great before, but I got error after window update today

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

Kurt_Bremser — Mon, 21 Feb 2022 23:12:42 GMT

Please post the complete log of the PROC IMPORT with DBMS=EXCEL by copy/pasting it into a window opened with this button:

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

Brian16 — Tue, 22 Feb 2022 14:01:53 GMT

proc import datafile = "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx"
			out = work.Variants
			dbms= XLSX replace;
		RANGE = "Most Recent$";
		GETNAMES=YES;
		MIXED=YES;
		SCANTEXT=YES;
		USEDATE=YES;
		SCANTIME=YES; 
run;

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

PaigeMiller — Tue, 22 Feb 2022 14:04:17 GMT

@Brian16

This is not the log, which is what was requested.

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

Brian16 — Tue, 22 Feb 2022 14:52:42 GMT

Here is the correct log


28         proc import datafile = "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx"
29         			out = work.Variants
30         			dbms= XLSX replace;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
31         		RANGE = "Most Recent$";
NOTE: The previous statement has been deleted.
32         		GETNAMES=YES;
33         		MIXED=YES;
             _____
             180
34         		SCANTEXT=YES;
             ________
             180
35         		USEDATE=YES;
             _______
             180
36         		SCANTIME=YES;
             ________
             180
ERROR 180-322: Statement is not valid or it is used out of proper order.

37         run;

Couldn't find range in spreadsheet
Requested Input File Is Invalid
2                                                          The SAS System                           08:48 Tuesday, February 22, 2022

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.31 seconds
      cpu time            0.00 seconds
      
38         
39         %LET _CLIENTTASKLABEL=;
40         %LET _CLIENTPROCESSFLOWNAME=;
41         %LET _CLIENTPROJECTPATH=;
42         %LET _CLIENTPROJECTPATHHOST=;
43         %LET _CLIENTPROJECTNAME=;
44         %LET _SASPROGRAMFILE=;
45         %LET _SASPROGRAMFILEHOST=;
46         
47         ;*';*";*/;quit;run;
48         ODS _ALL_ CLOSE;
49         
50         
51         QUIT; RUN;
52

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

PaigeMiller — Tue, 22 Feb 2022 14:59:13 GMT

As stated by @Kurt_Bremser you need to use DBMS=EXCEL, not DBMS=XLSX

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

Brian16 — Tue, 22 Feb 2022 15:08:27 GMT

When I run it with EXCEL it gives me this error. It worked with EXCEL before but gave me error for yesterday and today. (Yesterday, window was update automatically)

28         proc import datafile = "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx"
29         			out = work.Variants
30         			dbms= EXCEL replace;
31         		RANGE = "Most Recent$";
32         		GETNAMES=YES;
33         		MIXED=YES;
34         		SCANTEXT=YES;
35         		USEDATE=YES;
36         		SCANTIME=YES;
37         run;

1                                                          The SAS System                           09:05 Tuesday, February 22, 2022

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed.  See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           1.73 seconds
      cpu time            0.04 seconds
      
38         
39         %LET _CLIENTTASKLABEL=;
40         %LET _CLIENTPROCESSFLOWNAME=;
41         %LET _CLIENTPROJECTPATH=;
42         %LET _CLIENTPROJECTPATHHOST=;
43         %LET _CLIENTPROJECTNAME=;
44         %LET _SASPROGRAMFILE=;
45         %LET _SASPROGRAMFILEHOST=;
46         
47         ;*';*";*/;quit;run;
2                                                          The SAS System                           08:48 Tuesday, February 22, 2022

48         ODS _ALL_ CLOSE;
49         
50         
51         QUIT; RUN;
52

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

Tom — Tue, 22 Feb 2022 15:18:36 GMT

Just remove the statements that are not supported. They aren't doing anything for you now (and probably did not do anything for you when you did have access to actually run Excel). Now you code is more portable as it will work on Unix also.

proc import datafile = "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx"
  dbms= XLSX
  out = work.Variants
  replace
;
  RANGE = "Most Recent$";
run;

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

Brian16 — Tue, 22 Feb 2022 15:34:54 GMT

I tried that but still have error.

55         proc import datafile = "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx"
56         		dbms= XLSX
57         		out = work.Variants
58         			 replace;
59         		RANGE = "Most Recent$";
60         		
61         run;

Couldn't find range in spreadsheet
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.24 seconds
      cpu time            0.04 seconds
      
62         
63         %LET _CLIENTTASKLABEL=;
64         %LET _CLIENTPROCESSFLOWNAME=;
65         %LET _CLIENTPROJECTPATH=;
66         %LET _CLIENTPROJECTPATHHOST=;
67         %LET _CLIENTPROJECTNAME=;
3                                                          The SAS System                           09:27 Tuesday, February 22, 2022

68         %LET _SASPROGRAMFILE=;
69         %LET _SASPROGRAMFILEHOST=;
70         
71         ;*';*";*/;quit;run;
72         ODS _ALL_ CLOSE;
73         
74         
75         QUIT; RUN;
76

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

Tom — Tue, 22 Feb 2022 15:42:28 GMT

So what ranges do exist in the file you are using? Try using the XLSX libname engine instead.

libname x xlsx "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx";
proc copy inlib=x output=work;
run;

Check the NOTE lines from the PROC COPY step to see what sheets and named ranges were found.

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

Brian16 — Tue, 22 Feb 2022 16:41:37 GMT

I still have error with the change

468 libname x xlsx "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx";
NOTE: Libref X was successfully assigned as follows:
Engine: XLSX
Physical Name: K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx
469 proc copy inlib=x output=work;
------
1
WARNING 1-322: Assuming the symbol OUT was misspelled as output.
470 run;

NOTE: Copying X.20210802 to WORK.20210802 (memtype=DATA).
NOTE: Variable Name Change. Variant Type -> Variant_Type
NOTE: Variable Name Change. Snomed Code -> Snomed_Code
NOTE: Variable Name Change. Date Requested -> Date_Requested
NOTE: Variable Name Change. Date Completed in HEDSS -> Date_Completed_in_HEDSS
NOTE: Variable Name Change. Variant Type_1 -> Variant_Type_1
NOTE: Variable Name Change. Common Name -> Common_Name
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
ERROR: The value 20210802 is not a valid SAS name.
ERROR: File WORK.'20210802'n.DATA has not been saved because copy could not be completed.
NOTE: Copying X.20210810 to WORK.20210810 (memtype=DATA).
NOTE: Variable Name Change. Variant Type -> Variant_Type
NOTE: Variable Name Change. Snomed Code -> Snomed_Code
NOTE: Variable Name Change. Date Requested -> Date_Requested
NOTE: Variable Name Change. Date Completed in HEDSS -> Date_Completed_in_HEDSS
NOTE: Variable Name Change. Variant Type_1 -> Variant_Type_1
NOTE: Variable Name Change. Common Name -> Common_Name
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
ERROR: The value 20210810 is not a valid SAS name.
ERROR: File WORK.'20210810'n.DATA has not been saved because copy could not be completed.
NOTE: Copying X.MOST RECENT to WORK.MOST RECENT (memtype=DATA).
NOTE: Variable Name Change. Variant Type -> Variant_Type
NOTE: Variable Name Change. Snomed Code -> Snomed_Code
NOTE: Variable Name Change. Date Requested -> Date_Requested
NOTE: Variable Name Change. Date Completed in HEDSS -> Date_Completed_in_HEDSS
NOTE: Variable Name Change. Variant Type_1 -> Variant_Type_1
NOTE: Variable Name Change. Common Name -> Common_Name
NOTE: Variable Name Change. Updated in SARS Code List -> Updated_in_SARS_Code_List
NOTE: BUFSIZE is not cloned when copying across different engines.
System Option for BUFSIZE was used.
ERROR: The value MOST RECENT is not a valid SAS name.
ERROR: File WORK.'MOST RECENT'n.DATA has not been saved because copy could not be completed.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.37 seconds
cpu time 0.07 seconds

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

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

Kurt_Bremser — Tue, 22 Feb 2022 16:49:36 GMT

This points to a bitness issue between SAS and MS Office. Both must be either 32 or 64 bit.

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

ballardw — Tue, 22 Feb 2022 16:53:02 GMT

When you see this error:

ERROR: The value 20210802 is not a valid SAS name.

You may need to set the system option VALIDMEMNAME=extend; before running the code to allow the use of non-standard SAS data set names. It seems to almost be a rule in the Excel community to name sheets and columns with things that don't work well in SAS as data set or variable names.

I might suggest renaming the data sets after they are read

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

Kurt_Bremser — Tue, 22 Feb 2022 16:53:43 GMT

So your sheet is named "Most recent". See if this works:

proc import
  datafile = "K:\Variant Taskforce\Variant Codes\Variant Types and Snomed_V20220126.xlsx"
  dbms= XLSX
  out = work.Variants
  replace
;
sheet = "Most Recent";
run;

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

Brian16 — Tue, 22 Feb 2022 19:09:23 GMT

Thank you so much Kurt, it work perfectly.