Hmm... good catch. I added DBMSTEMP=YES; to connection string however same error. It must be possible though because SAS indicates support. Will continue to search for other examples. /* Get credentials */
%get_system_credentials(source="xxxxxxxx", envir="prod");
/* Snowflake libname connection */
LIBNAME SNOW ODBC COMPLETE = "
DRIVER=SnowflakeDSIIDriver;
AUTHENTICATOR=SNOWFLAKE_JWT;
SERVER=xxxxxxxx;
UID=&userid;
PRIV_KEY_FILE=&spwd;
PRIV_KEY_FILE_PWD=;
Warehouse=xxxxxxxx;
Role=&datasrc;
Database=xxxxxxxx;
CONNECTION=GLOBAL;
DBMSTEMP=YES;
";
PROC SQL NOPRINT;
/* Connect to Snowflake */
CONNECT USING SNOW;
/* Create temporary table on Snowflake */
EXECUTE by SNOW (USE DATABASE xxxxxxxx;);
EXECUTE by SNOW (USE SCHEMA DATA;);
EXECUTE by SNOW (
CREATE TEMPORARY TABLE TMP_TABLE (HHID NUMBER(13,0))
ON COMMIT PRESERVE ROWS;
);
EXECUTE by SNOW (
COMMIT WORK;
);
EXECUTE by SNOW (
INSERT INTO TMP_TABLE (HHID)
VALUES (311100);
);
/* Create SAS Work table from temporary Snowflake table */
CREATE TABLE WORK.TMP_TABLE_SAS AS
SELECT *
FROM SNOW.TMP_TABLE;
QUIT;
... View more