topic Re: how to use a SAS macro variable in a SQL pass through connection with snowflake database in SAS Enterprise Guide

how to use a SAS macro variable in a SQL pass through connection with snowflake database

alepage — Wed, 19 Nov 2025 15:17:24 GMT

Hello,

Below, you will see an example of a SQL pass through connection to a snowflake database and it works very well.

%macro test(value);
%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;
%mend test;
%test(be);

But if I want to use a case statement, how do we pass the &value. in a Snowflake SQL script?


%macro test(value);
%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 * ,
               case
               when &value. = 'be' then '010'
               when &value. = 'gc' then '001'
               End as newvar


     from TEMPTBL
);
             
        disconnect from snow;
quit;
%mend test;
%test(be);

I did some test and I know that the &value. is throwing an error.

How to solve that issue.

Re: how to use a SAS macro variable in a SQL pass through connection with snowflake database

PaigeMiller — Wed, 19 Nov 2025 16:05:07 GMT

Whenever SAS encounters a macro variable in code, it replaces the macro variable with its actual value, and then runs the code with these replacements made. So, for this code:

     select * ,
               case
               when &value. = 'be' then '010'
               when &value. = 'gc' then '001'
               End as newvar

This is the code that is actually executed:

     
     select * ,
               case
               when be = 'be' then '010'
               when be = 'gc' then '001'
               End as newvar

SQL thinks BE is the name of a variable in the table. (Do you understand why SAS thinks BE is the name of a variable in the table?) So this code only makes sense if the table that SQL is working on (TEMPTBL) has a variable named BE. Does it?

A much better approach to create programs with macro variables is to get the code to work without macro variables first, and then substitute the macro variables into the code. But you haven't done that, and so it is highly recommended.

So, please tell us, what is the code you want to have executed here, without macro variables.

Finally, when you are getting errors in the log, please show us (ALWAYS) the relevant parts of the log.

Re: how to use a SAS macro variable in a SQL pass through connection with snowflake database

alepage — Wed, 19 Nov 2025 16:22:33 GMT

%macro test(value); %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 * , case when %str(%')&value.%str(%') = 'be' then '010' when %str(%')&value.%str(%') = 'gc' then '001' else 'Other' End as newvar from TEMPTBL ); disconnect from snow; quit; %mend test; %test(be);

Re: how to use a SAS macro variable in a SQL pass through connection with snowflake database

PaigeMiller — Wed, 19 Nov 2025 16:33:27 GMT

This is even easier than your solution

     select * ,
               case
               when "&value" = 'be' then '010'
               when "&value" = 'gc' then '001'
               else 'Other'
               end as newvar

Re: how to use a SAS macro variable in a SQL pass through connection with snowflake database

alepage — Wed, 19 Nov 2025 18:22:34 GMT

No! The SQL query in Snowflake does not take "&Value"

Re: how to use a SAS macro variable in a SQL pass through connection with snowflake database

Kurt_Bremser — Wed, 19 Nov 2025 19:41:08 GMT

So you basically want to set a costant value. Let the macro processor do it:

     select * ,
%if &value. = be %then '010';
%else %if &value. = gc %then '001';
%else 'xxx';
               as newvar