BookmarkSubscribeRSS Feed
Obsidian | Level 7

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


DBMSTEMP= LIBNAME Statement Option

Specifies whether SAS creates temporary or permanent tables.

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

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
Opal | Level 21

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

Obsidian | Level 7





No change. 


Cheers, Don

Obsidian | Level 7

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

Tourmaline | Level 20

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?

Data never sleeps
Opal | Level 21

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;
Obsidian | Level 7
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.
Opal | Level 21

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. 

Obsidian | Level 7

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

Obsidian | Level 7

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 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.

Opal | Level 21

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



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. 

Register now!

How to Concatenate Values

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.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 3 in conversation