strange error message

Batman — Thu, 15 Jun 2023 17:00:33 GMT

I've run this data step before without incident, but now I'm getting an error I don't understand.

28 DATA SASTEMP.ICD10_AMPUTATION;
29 SET A3X5_LIB.ICD10_AMPUTATION;
30 RUN;

SNOWFLAKE_17: Prepared: on connection 4
SELECT * FROM CMS_SASTEMP_COMM_PRD.ICD10_AMPUTATION WHERE 0=1

Summary Statistics for SNOWFLAKE are:
Total SQL prepare seconds were: 0.165240
Total seconds used by the SNOWFLAKE ACCESS engine were 0.167861

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

SNOWFLAKE_18: Executed: on connection 5
CREATE TABLE CMS_SASTEMP_COMM_PRD.ICD10_AMPUTATION (Order number double,ICD10 VARCHAR(7),Long description VARCHAR(58))

ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: SQL compilation error: syntax error line 1 at position 52
unexpected 'Order'. syntax error line 1 at position 106 unexpected 'VARCHAR'. syntax error line 1 at position 113 unexpected
'('. syntax error line 1 at position 117 unexpected ')'..
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set SASTEMP.ICD10_AMPUTATION may be incomplete. When this step was stopped there were 0 observations and 3
variables.

Summary Statistics for SNOWFLAKE are:
Total SQL execution seconds were: 0.069006
Total SQL prepare seconds were: 0.165240
2 The SAS System 12:18 Thursday, June 15, 2023

Total seconds used by the SNOWFLAKE ACCESS engine were 0.239782

ERROR: ROLLBACK issued due to errors for data set SASTEMP.ICD10_AMPUTATION.DATA.
NOTE: DATA statement used (Total process time):
real time 2.46 seconds
cpu time 0.09 seconds

Re: strange error message

Quentin — Thu, 15 Jun 2023 17:13:09 GMT

I don't use snowflake, so this is just a guess. But from the logs, it looks like there might be a column named Order number in your source snowflake table, and that is tripping up the generated SQL which thinks ORDER is a keyword instead of part of a variable name.

You could try stuff like:

DATA SASTEMP.ICD10_AMPUTATION;
  SET A3X5_LIB.ICD10_AMPUTATION (rename=("Order Number"N = OrderNumber)) ;
RUN;

topic strange error message in SAS Programming

strange error message

Re: strange error message