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 ×tamp. as contractVer
join CONTRACTPL_DM.LKP_PROGRAM ×tamp. 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 ¤t_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 ×tamp. as contractVer
join CONTRACTPL_DM.LKP_PROGRAM ×tamp. 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 ).
... View more
