Trying to setup a libname for Snowflake as follows:
libname fch_data snow
29 libname fch_data snow dbmstemp=YES
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|
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') ;
data fch_data.mysasfile ; <-- SNow libname
set mysasfile ;
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
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.
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.
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.