topic Re: SAS to Snowflake data load using Key Pair in SAS Programming

SAS to Snowflake data load using Key Pair

freshstarter — Tue, 12 May 2026 16:11:00 GMT

Hello All,

I’m trying to write a SAS program to load data from a SAS dataset into Snowflake using key-pair authentication.

We have defined the LIBNAME statement in the backend configuration so that the Snowflake library is automatically assigned without specifying the role, warehouse, schema, and database. This is because multiple teams use the same setup, and each team has its own role, warehouse, and other parameters. Therefore, we intentionally excluded these details from the LIBNAME definition.

Instead, we have asked users to specify the required context (role, warehouse, database, schema) within their SAS code. However, when attempting to load data from a SAS dataset into Snowflake using this approach, we have been unable to find the correct SAS syntax. We have tried several approaches, but none have worked successfully.

Below code uses snow libref which is defined in backend and automatically assigned when the session starts.

libname perm "/mnt/test/_shared";
%let DB=SANDBOX_DATABASE;
%let schema=SANDBOX_SCHEMA;

proc sql ;
connect using snow as snow;
execute ( USE ROLE SANDBOX_ROLE) by snow;
execute ( USE WAREHOUSE SANDBOX_VWH ) by snow;

quit;

data snow."&DB"."&SCHEMA"."cars1"n;
    set perm.test_dataset;
run;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.  
ERROR 201-322: The option is not recognized and will be ignored.

Since we are using key-pair authentication, we are unable to share details such as the private key file location and encryption key. This is why the LIBNAME is configured in the backend, and users are only expected to define role/warehouse/database/schema in their code.

At this point, we are unsure how to proceed and would appreciate any guidance or best practices to resolve this issue.

Thanks in advance for your help.

Re: SAS to Snowflake data load using Key Pair

SASKiwi — Tue, 12 May 2026 20:15:48 GMT

PERM is the only LIBREF you have defined for Snowflake. If you want to read a SAS table and write it to Snowflake in a DATA step the syntax should look like this:

data perm.cars;
    set sashelp.cars;
run;

Re: SAS to Snowflake data load using Key Pair

freshstarter — Tue, 12 May 2026 20:32:04 GMT

Thanks @SASKiwi for your response.

Currently the SAS dataset is stored in the library called PERM which points to this location /mnt/test/_shared. Snowflake library called SNOW is defined in the autoexec file. something like below which do not have role,warehouse, database and schema.

LIBNAME SNOW SNOW
dsn=snowflake
CONOPTS="AUTHENTICATOR=SNOWFLAKE_JWT;
PRIV_KEY_FILE=/LOCATION/OF/KEY/rsa_key.p8;
PRIV_KEY_FILE_PWD=XXXXXXXXXXX;"

As I mentioned before , I cant define the role ,database , warehouse and schema in the libname statement and we planned to use these in SAS code after the libname is assigned.

But below code didnot work for us. We tried to pass the database , schema in the data step.

proc sql ;
connect using snow as snow;
execute ( USE ROLE SANDBOX_ROLE) by snow;
execute ( USE WAREHOUSE SANDBOX_VWH ) by snow;

quit;

data snow."&DB"."&SCHEMA"."cars1"n;
    set perm.test_dataset;
run;

Re: SAS to Snowflake data load using Key Pair

SASKiwi — Tue, 12 May 2026 21:29:00 GMT

If you use a DATA step with Snowflake then the database, and schema must be defined in the LIBNAME statement. You can only use a LIBREF and a table name in the DATA statement like in the example I posted.

Re: SAS to Snowflake data load using Key Pair

Tom — Tue, 12 May 2026 23:37:05 GMT

Don't know about ROLE, but why not try using the SCHEMA= dataset option.

data snow.mytable(schema="myschema");
  set perm.mydataset;
run;