BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Batman
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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;
The Boston Area SAS Users Group (BASUG) is hosting an in person Meeting & Training on June 27!
Full details and registration info at https://www.basug.org/events.

View solution in original post

1 REPLY 1
Quentin
Super User

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;
The Boston Area SAS Users Group (BASUG) is hosting an in person Meeting & Training on June 27!
Full details and registration info at https://www.basug.org/events.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1681 views
  • 0 likes
  • 2 in conversation