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
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;
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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.