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;
BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: 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;
BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

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
  • 1 reply
  • 428 views
  • 0 likes
  • 2 in conversation