topic Re: Replacing Worktables Automatically when Loading from SAS DI Studio to Snowflake in SAS Programming

Replacing Worktables Automatically when Loading from SAS DI Studio to Snowflake

jozumhannes — Wed, 11 Oct 2023 12:48:59 GMT

Hello everyone,

I'm currently facing an issue with SAS Data Integration Studio while attempting to upload tables to Snowflake, including the worktables generated during transformations.

The Problem: The connection to Snowflake is established successfully, and I can upload tables without any problem. However, my main challenge lies with worktables. When I execute the job multiple times, I receive an error indicating that the table already exists. I want the worktables to be replaced automatically instead of SAS Data Integration Studio attempting to create them again.

In my SAS Data Integration Studio transformation Extract, I've used the following LIBNAME statement:

LIBNAME snowlib SASIOSNF DATABASE=snowflakedatabase DSN=snowflake SCHEMA=PUBLIC AUTHDOMAIN="XXX" ;

The transformation was executed once, and the worktable is now on Snowflake. However, I can't execute the transformation multiple times due to the error message indicating that the table already exists.

Is there a way to configure the library settings so that the tables are automatically replaced when the job is executed again?

Re: Replacing Worktables Automatically when Loading from SAS DI Studio to Snowflake

LinusH — Mon, 16 Oct 2023 14:20:32 GMT

For work tables, i suggest that you take a look at the DBMSTEMP= option.

That itself might not solve your problem if you wish to rerun a transformation interactively in DI Studio. Disconnecting from your workspace server or de-assigning the libref would remove the work table though.

What you really want is a way to specify something corresponding to

CREATE OR REPLACE

in Snowflake. Not sure how to do that without having to resort to user written code...

There's a REPLACE= data set option, but I don't think works for anything other than permanent SAS datasets.

Re: Replacing Worktables Automatically when Loading from SAS DI Studio to Snowflake

jozumhannes — Mon, 16 Oct 2023 16:02:47 GMT

I'd like to thank you for pointing out the DBMSTEMP= option, which was indeed helpful for managing the Work Tables. However, I'm still in search of an optimal solution for handling permanent tables in Snowflake. I'm looking for a way to execute the job multiple times without encountering the error that the table already exists. If anyone has any suggestions or ideas on how to achieve this, I'd greatly appreciate further insights.