Inserting SAS EG Data into a Snowflake Temporary Table - Stack Overflow

admin管理员组

文章数量:1287897

I am having trouble creating a temporary table on Snowflake and inserting data into it from my SAS dataset. My goal is to pull relevant Snowflake data so that I can match it to members that exist in my SAS dataset.

(I am very new to SAS, so I will detail my steps below as clearly as how I understand it, thank you)

I connect to Snowflake using the code below, which takes my Snowflake credentials and creates a library with that information. This all works fine and my libname is assigned.

options noquotelenmax symbolgen;
%let ID         =  /* e-mail address */
%let PRKF       = /* path to private key file, including private key file name */
%let RL         =  /* role */
%let dbpath = /* server address */
%let drvr       =  SnowflakeDSIIDriver;

libname EXAMPLE ODBC complete="
driver=&drvr;
authenticator=SNOWFLAKE_JWT;
server=&dbpath;
UID=&ID;
priv_key_file=&PRKF;
role=&RL;
warehouse= /*warehouse name*/;
database= /*database name*/;
schema= /*schema name*/;";

In my SAS file, I have created two libnames: One libname is to reference the SAS dataset that I am working with, and the second libname is to reference the Snowflake database and schema that I want to pull data from. I'm not really sure if that second database is needed (since I created a libname for that same database and schema), but I run it just in case. This works fine too!

libname sasdata /*SAS dataset*/;
LIBNAME EXAMPLE SASIOSNF SERVER="&dbpath."
DATABASE=/*database name*/
SCHEMA=/*schema name*/
WAREHOUSE=/*warehouse name*/
ROLE=/*role name*/
CONOPTS="UID=&ID.;AUTHENTICATOR=SNOWFLAKE_JWT;PRIV_KEY_FILE=&PRKF.;"
CONNECTION=GLOBAL DBCOMMIT=20000 AUTOCOMMIT=NO READBUFF=32000 INSERTBUFF=32000;

Okay, I have created my libnames that I can use to pull data from Snowflake, and so now I want to create a temporary table in Snowflake to house the data from SAS. That way, I can match to those specific members I am interested in, and pull them back into SAS EG to work with. SOMETIMES this code works for me, and many times it does not, and it says "ERROR: File (File Name).DATA does not exist." I do not know why it works sometimes and other times it doesn't...

PROC SQL;
  CONNECT USING EXAMPLE;
  EXECUTE(CREATE OR REPLACE TEMPORARY TABLE %UPCASE(&SYSUSERID.)_MBRLIST
    (
    MBR_ID VARCHAR(50)
    ) ON COMMIT PRESERVE ROWS) BY EXAMPLE;
  INSERT INTO EXAMPLE.%UPCASE(&SYSUSERID.)_MBRLIST
     SELECT
    MBR_ID
  FROM sasdata.SAS_DATA;
      CREATE TABLE sasdata.SAS_AND_SNOWFLAKE_MATCH AS
  SELECT * FROM CONNECTION TO EXAMPLE
  (SELECT A.*
  FROM SNOWFLAKE_TABLE A
  INNER JOIN %UPCASE(&SYSUSERID.)_MBRLIST B
        ON A.MBR_ID = B.MBR_ID
        );
QUIT;

本文标签： Inserting SAS EG Data into a Snowflake Temporary TableStack Overflow