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

[Brian16]

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;

[Kurt_Bremser]

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

[Brian16]

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

[Kurt_Bremser]

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

[Brian16]

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;

[PaigeMiller]

@Brian16 

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

[Brian16]

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         

[PaigeMiller]

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

[Brian16]

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         

[Kurt_Bremser]

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

[Tom]

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;

[Brian16]

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         

[Tom]

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.

[Brian16]

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.

[ballardw]

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