SQL Pass-Through Facility Specifics for Snowflake

Key Information

For general information about this feature, see SQL Pass-Through Facility.

Here are the SQL pass-through facility specifics for the Snowflake interface.

The dbms-name is snow.
The CONNECT statement is required.
PROC SQL supports multiple connections to Snowflake. If you use multiple simultaneous connections, you must use the alias argument to identify the different connections. If you do not specify an alias, the default snow alias is used.
The CONNECT statement database-connection-arguments are identical to its LIBNAME connection options.
Snowflake does not support indexes. Statements that pertain to indexes, such as the CREATE INDEX statement, are not supported.

IMPORTANT Issuing multiple INSERT commands within PROC SQL or PROC FEDSQL might result in slow performance. To insert multiple records, it is therefore recommended that you use a method that inserts them all at once, such as one these methods.

a single INSERT command to insert multiple records
a DATA step to copy multiple records from another source or from cards input
bulk loading, when available

Note: A FedSQL INSERT statement can insert values for only one row at a time. To insert multiple rows, you need multiple INSERT statements.

CONNECT Statement Examples

This example connects to Snowflake and then disconnects from it.

proc sql noerrorstop;
   connect to snow as x1(server=mysrv1 port=2406 
      user=mysur1 password='mypwd1' database=mydb1);
disconnect from x1;
quit;

This next example connects to Snowflake, executes some SQL statements, and then disconnects from Snowflake.

proc sql noerrorstop;
 connect to snow as x1(server=mysrv1 port=2406 
    user=mysur1 password='mypwd1' database=mydb1);

 execute ( CREATE TABLE t1 ( no int primary key, state varchar(10) ) ) by x1;
 execute ( INSERT INTO t1 values (1, 'USA') ) by x1;
 execute ( INSERT INTO t1 values (2, 'CHN') ) by x1;
 select * from connection to x1 (SELECT * FROM t1 ORDER BY no);

 disconnect from x1;
 quit;

The Snowflake engine can process CREATE TABLE table-name AS SELECT in a single step. Using the DBIDIRECTEXEC system option, the SQL procedure can pass CREATE TABLE AS SELECT statements to Snowflake. By default, the system option is specified for the Snowflake engine. In this example, the schema is specified in the DSN.

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;
libname x snow dsn='snowflake' user=sasuser pw=XXXXXXXX schema=myschema;
 
proc delete data=x.class1; run;
proc delete data=x.class2; run;
 
data x.class1;
  set sashelp.class;
run;
 
proc sql;
  create table x.class2 as select * from x.class1;
quit;

Here is how the above code is passed.

CREATE TABLE "SCHEMA1"."class2" AS
 SELECT TXT_1."Name", TXT_1."Sex", TXT_1."Age", TXT_1."Height", TXT_1."Weight"
 FROM "SCHEMA1"."class1" TXT_1

If the NODBIDIRECTEXEC option is specified, the statement is executed in multiple steps, transferring the data to and from SAS.

CREATE TABLE "SCHEMA1"."class2"
  ("Name" VARCHAR(8),"Sex" VARCHAR(1),"Age" DOUBLE,"Height" DOUBLE,"Weight" DOUBLE)
 
INSERT INTO "SCHEMA1"."class2"
  ("Name","Sex","Age","Height","Weight")
  VALUES ( ? , ? , ? , ? , ? )

Last updated: February 3, 2026