topic Re: SAS Access to Snowflake in SAS Procedures

SAS Access to Snowflake

garyhaas — Tue, 22 Jun 2021 22:58:42 GMT

Want to use Bulkload to an existing Snowflake table that has been created using a Create table statement. But libname bulk load complains that table already exists. Is there a way to use bulkload using a connect statement with execute commands?

Re: SAS Access to Snowflake

SASKiwi — Tue, 22 Jun 2021 23:39:13 GMT

Can you please post the SAS log including code and notes?

Re: SAS Access to Snowflake

garyhaas — Thu, 24 Jun 2021 16:05:14 GMT

Need to pre-define table with Create table statement, not delete it before load

219 data mydblib.zipterr_rbu_&qtr
220 (bulkload=yes
221 bl_internal_stage="user/someuser"
222 BL_COMPRESS=yes);
223 set idata.rbu_&terr._zip
224 ;

SNOWFLAKE: AUTOCOMMIT turned ON for connection id 1

SNOWFLAKE_1: Prepared: on connection 1
SELECT * FROM "CDW_US_ASB_GBA_DB"."zipterr_rbu_Q221b" WHERE 0=1

DBMS_TIMER: summary statistics
DBMS_TIMER: total SQL prepare seconds were: 0
DBMS_TIMER: dbiopen/dbiclose timespan was 0.
ERROR: The SNOWFLAKE table zipterr_rbu_Q221b has been opened for OUTPUT. This table already exists, or there is a name
conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE
option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode.
This prevents execution of subsequent data modification statements.
NOTE: DATA statement used (Total process time):
real time 2.62 seconds
cpu time 0.75 seconds

Re: SAS Access to Snowflake

SASKiwi — Thu, 24 Jun 2021 21:10:20 GMT

Just to clarify, are you just appending rows here? If so I suggest you try PROC DATASETS with the APPEND statement where the BASE table is pre-defined table and the APPEND table is your SAS table.

Re: SAS Access to Snowflake

garyhaas — Thu, 24 Jun 2021 21:19:12 GMT

Yes, that was the advice of Tech support and it works. I used PROC APPEND rather than PROC DATASETS but that shouldn't matter.

Thank you for the advice!

Re: SAS Access to Snowflake

SASKiwi — Thu, 24 Jun 2021 21:28:28 GMT

Great, its the same thing. Please mark your post as answered then.