topic Re: DBMSTEMP option under LIBNAME for Snowflake in SAS Programming

DBMSTEMP option under LIBNAME for Snowflake

bitcruncher — Wed, 26 Apr 2023 01:00:40 GMT

Trying to setup a libname for Snowflake as follows:

libname fch_data snow

dbmstemp=YES
server="<<serverName>>"
database=<<dbname>>
user=<<username>>
role=<<rolename>>
pw=<<pwname>>
schema=<<schemaname>>
connection=global
conopts="<<proxyname";

29 libname fch_data snow dbmstemp=YES
________
22
ERROR 22-7: Invalid option name DBMSTEMP.

If I remove DBMSTEMP the libname works fine. I only need it to allow a libname to create temporary tables in Snowflake so if there is another way of doing that from data step I can try that, but cant see any obvious way apart from libname and executing CREATE TEMP in SQL (thats not my preference, the temp table source is always a SAS DB and can be very large, also I cant see any way using execute to create a Snowflake table from a SAS table -- not sure I'd want to).

This is in

Current version: 9.04.01M7P080520
Operating System: WX64_SV .

DBMSTEMP is documented as valid in SAS/ACCESS to SnowFlake at https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0he4t6yjfmkhpn16qrf0cdhllu6.htm

DBMSTEMP= LIBNAME Statement Option

Specifies whether SAS creates temporary or permanent tables.

Valid in:Category:Default:Requirement:Interaction:Data source:

SAS/ACCESS LIBNAME statement

Data Set Control

To specify this option, you must first specify CONNECTION=GLOBAL—except for Microsoft SQL Server, which defaults to UNIQUE.

To access temporary tables, set DBMSTEMP= to YES and set the CONNECTION= LIBNAME option to GLOBAL.

Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick

Re: DBMSTEMP option under LIBNAME for Snowflake

SASKiwi — Wed, 26 Apr 2023 03:18:00 GMT

What happens if you move the CONNECTION = GLOBAL option before the DBMSTEMP one?

Re: DBMSTEMP option under LIBNAME for Snowflake

bitcruncher — Wed, 26 Apr 2023 04:34:06 GMT

Thanks

No change.

Cheers, Don

Re: DBMSTEMP option under LIBNAME for Snowflake

bitcruncher — Wed, 26 Apr 2023 04:46:55 GMT

I should point out that this is part of a POC to show how to drop SAS files on Snow as temp files. The ultimate aim is something along the lines of

data mysasfile ; <-- local SAS dataset
length myvalue $256 ;
myvalue = pathname('work') ;
run ;

data fch_data.mysasfile ; <-- SNow libname
set mysasfile ;
run ;

so that we have driver files derived from local databases to set up initial keys for a following Snowflake query to return data from.

I cant see how to do this in SQL right now, but I can create temp tables in Snow easily with CREATE TEMP TABLE in the Snow query in SQL. This doesnt need DBMSTEMP=YES. I need to be able to understand if a SAS dataset can copy straight to Snow, preferably without specifying the file attributes and having inserts. Thats why the datastep approach is quite appealing, assuming I can make it work and DBMSTEMP is the first obstacle

Re: DBMSTEMP option under LIBNAME for Snowflake

LinusH — Thu, 27 Apr 2023 07:20:19 GMT

If I try to intepret the documentation it seems like dbmstemp has not always been availabale in the ACESS interface.

What version are you running?

Re: DBMSTEMP option under LIBNAME for Snowflake

SASKiwi — Thu, 27 Apr 2023 07:58:55 GMT

Hmm, it was a long shot anyway. I suggest you check with Tech Support as the doc suggests it should be available. Maybe run this also to check your actual SAS/ACCESS product version:

proc product_status;
run;

Re: DBMSTEMP option under LIBNAME for Snowflake

bitcruncher — Thu, 27 Apr 2023 22:29:53 GMT

Unfortunately PRODUCT_STATUS does not give me any info on SAS/ACCESS to Snowflake. We installed the software in Feb this year, and documentation already told me that DBMSTEMP was available under Snowflake back in September last year when I listed that as a critical requirement.

Re: DBMSTEMP option under LIBNAME for Snowflake

SASKiwi — Thu, 27 Apr 2023 22:49:41 GMT

You should see something similar to this with Product_Status:

For SAS/ACCESS Interface to ODBC ...
   Custom version information: 9.4_M2

If you don't I'd be a bit worried that something is not quite right with the install.

Otherwise really a question for Tech Support then as the doc says its available but your tests suggest it is not.

Re: DBMSTEMP option under LIBNAME for Snowflake

bitcruncher — Thu, 27 Apr 2023 22:59:14 GMT

Thanks, So ODBC product covers Snowflake, or you are using that as an example?

ODBC is there

For SAS/ACCESS Interface to ODBC

Custom version information: 9.4_M7

but nothing specific to Snowflake

Yes, am talking to SAS Support today .. thanks

Re: DBMSTEMP option under LIBNAME for Snowflake

bitcruncher — Fri, 28 Apr 2023 00:50:07 GMT

SAS have a hot fix for this. It was available when we had the Snowflake interface installed but was missed. I also had not been able to find when doing searches in the support pages. The hotfix is https://support.sas.com/kb/68/155.html and of course now I can see it jumping out at me in search results.

When installed and verified working I'll mark this solved. Thanks people for helping me with this.

Re: DBMSTEMP option under LIBNAME for Snowflake

SASKiwi — Fri, 28 Apr 2023 02:36:48 GMT

Yes, I just posted ODBC as an example. My expectation was that SAS/ACCESS to Snowflake would report something similar.