BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Brian16
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

16 REPLIES 16
Brian16
Calcite | Level 5
It was EXCEL instead of XLSX. I worked great before, but I got error after window update today
Brian16
Calcite | Level 5
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
Diamond | Level 26

@Brian16 

 

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

--
Paige Miller
Brian16
Calcite | Level 5
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
Diamond | Level 26

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

--
Paige Miller
Brian16
Calcite | Level 5
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         
Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 16 replies
  • 5129 views
  • 1 like
  • 5 in conversation