how to create a table using a with tablename as statement with a snow flake pass trough connection

[alepage]

Hello,

I am using a SQL pass through connection to create the table TEMPTBL (see the script below).  Unfortunately, this table will be created in work, not into the selected schema.  So, If I want to make a left join with this table in another SQL pass through query, the table is not into the selected schema.

How to convert the script below to something using with TEMPTBL AS  statement.

%LET cn_SNOW = database=&databaseName.
SQL_FUNCTIONS=ALL
SERVER="&serverName."
SCHEMA=&schema.
AUTHDOMAIN=&authdomain.;


proc sql;
connect to snow(&cn_SNOW.);
     %check_table(work,TEMPTBL);
     create table TEMPTBL as
      select * from connection to snow 
     (
      select 	contractVer.DIM_PL_CNTRCT_VER_KEY,
               LKPPGM.*,
		     'O' as Affinity_Ind		

from CONTRACTPL_DM.DIM_PL_CNTRCT_VER &timestamp. as contractVer 
join CONTRACTPL_DM.LKP_PROGRAM &timestamp. as LKPPGM 
ON LKPPGM.DIM_PL_CNTRCT_VER_KEY = contractVer.DIM_PL_CNTRCT_VER_KEY
where LKPPGM.PROG_NM_CD='AFP' and LKPPGM.PROG_CAT_CD = 'INSBUSTRM'
order by contractVer.DIM_PL_CNTRCT_VER_KEY
     
     ) ;
             
        disconnect from snow;
quit;

How to convert that script to something like below

%LET cn_SNOW = database=&databaseName.
SQL_FUNCTIONS=ALL
SERVER="&serverName."
SCHEMA=&schema.
AUTHDOMAIN=&authdomain.;

/************************* Defining some Time travel macro variables    ******************/

%let current_day=%sysfunc(today(), yymmdd10.);
%let now = %sysevalf(%sysfunc(time()) + 120);
%let current_time=%sysfunc(putn(&now., time.));
%put &=current_time &=current_day;

%let my_date_formatted = %sysfunc(intnx(day, %sysfunc(today()), -7), yymmdd10.);
%put &=my_date_formatted;

%let timestamp=at(timestamp=> %str(%')&my_date_formatted  &current_time%str(%')::timestamp_ltz);
%put &=timestamp;

proc sql;
connect to snow(&cn_SNOW.);     
     
      select * from connection to snow 
     (
          WITH TEMPTBL as
          (
               select 	contractVer.DIM_PL_CNTRCT_VER_KEY,
                         LKPPGM.*,
                         'O' as Affinity_Ind		

               from CONTRACTPL_DM.DIM_PL_CNTRCT_VER &timestamp. as contractVer 
               join CONTRACTPL_DM.LKP_PROGRAM &timestamp. as LKPPGM 
               ON LKPPGM.DIM_PL_CNTRCT_VER_KEY = contractVer.DIM_PL_CNTRCT_VER_KEY
               where LKPPGM.PROG_NM_CD='AFP' and LKPPGM.PROG_CAT_CD = 'INSBUSTRM'
               order by contractVer.DIM_PL_CNTRCT_VER_KEY
            )
     
     ) ;
             
        disconnect from snow;
quit;

ERROR: CLI prepare error: SQL compilation error: syntax error line 1 at position 468 unexpected '<EOF>'.
SQL statement: WITH TEMPTBL as ( select contractVer.DIM_PL_CNTRCT_VER_KEY, LKPPGM.*, 'O' as Affinity_Ind from
CONTRACTPL_DM.DIM_PL_CNTRCT_VER at(timestamp=> '2025-11-11 10:33:20'::timestamp_ltz) as contractVer join
CONTRACTPL_DM.LKP_PROGRAM at(timestamp=> '2025-11-11 10:33:20'::timestamp_ltz) as LKPPGM ON LKPPGM.DIM_PL_CNTRCT_VER_KEY =
contractVer.DIM_PL_CNTRCT_VER_KEY where LKPPGM.PROG_NM_CD='AFP' and LKPPGM.PROG_CAT_CD = 'INSBUSTRM' order by
contractVer.DIM_PL_CNTRCT_VER_KEY ).

[alepage · Accepted solution]

This Script works and it is a with table statment.

%LET cn_SNOW = database=&databaseName.
SQL_FUNCTIONS=ALL
SERVER="&serverName."
SCHEMA=&schema.
AUTHDOMAIN=&authdomain.;

/************************* Defining some Time travel macro variables    ******************/

%let current_day=%sysfunc(today(), yymmdd10.);
%let now = %sysevalf(%sysfunc(time()) + 120);
%let current_time=%sysfunc(putn(&now., time.));
%put &=current_time &=current_day;

%let my_date_formatted = %sysfunc(intnx(day, %sysfunc(today()), -7), yymmdd10.);
%put &=my_date_formatted;

%let timestamp=at(timestamp=> %str(%')&my_date_formatted  &current_time%str(%')::timestamp_ltz);
%put &=timestamp;

proc sql;
connect to snow(&cn_SNOW.);     
 CREATE TABLE TEMP AS    
      select * from connection to snow 
     (
          WITH TEMPTBL as
          (
               select 	LKPPGM.*,
                         'O' as Affinity_Ind		

               from CONTRACTPL_DM.DIM_PL_CNTRCT_VER &timestamp. as contractVer 
               join CONTRACTPL_DM.LKP_PROGRAM &timestamp. as LKPPGM 
               ON LKPPGM.DIM_PL_CNTRCT_VER_KEY = contractVer.DIM_PL_CNTRCT_VER_KEY
               where LKPPGM.PROG_NM_CD='AFP' and LKPPGM.PROG_CAT_CD = 'INSBUSTRM'
               order by contractVer.DIM_PL_CNTRCT_VER_KEY
            )           
     
     select * from TEMPTBL

);
             
        disconnect from snow;
quit;

[Tom]

Right.  The issue was that what you put inside the ( ) after the CONNECTION TO xxx needs to be a complete statement.  The same thing will apply to a statement passed using the EXECUTE BY xxx syntax.