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

[alepage]

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.

[PaigeMiller]

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.

[alepage · Accepted solution]

%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);

[PaigeMiller]

This is even easier than your solution

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

[alepage]

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

[Kurt_Bremser]

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