How to create a MACRO variable in SAS library with options symbolgen

Question

I am really new to SAS and snowflake. Please correct my question, if you think there is a proper way to describe my question.

I need to build a SAS modules with following set up

options symbolgen;
%let SNOW = 
"DRIVER = {SnowflakeDSIIDriver};
 authenticator = https://xxx;
 UID = &user.;
 PWD = &pwd;
ROLE = ROLE_&sysuserid;
WAREHOUSE= AAA_BBB_CCC_WH;
DATABASE = XXX;
libname SF odbc complete = &SNOW."; 

The question I am facing is how to create a table that different users with different ROLE could create different table names without changing the code.

Ritht now I have to enter my name for each table I created as:

Create or replace table AA_BB.CC_Jeff as  /* my name is Jeff*/
 (select ...) 

My goal is to create the table as:

create table XXX.&SYSUSERID.&PROJ.yyy

&SUSUSERID should be able to auto-fill different user names to the table name.

Answer 1

First create a statement that works. So if the goal is to generate a statement like this statement:

libname SF odbc complete = 
  "DRIVER = {SnowflakeDSIIDriver};
   authenticator = https://xxx;
   UID = SF_USER;
   PWD = SF_PASS;
   ROLE = ROLE_SASUSER;
   WAREHOUSE= AAA_BBB_CCC_WH;
   DATABASE = XXX;
  "
; 

Then create macro variables that contain the parts that change and replace the example hardcoded values with references to the macro variables. So if there are three things that vary you need three macro variables.

%let db_user=SF_USER;
%let db_pass=SF_PASS;
%let sas_user=&sysuserid;

libname SF odbc complete = 
  "DRIVER = {SnowflakeDSIIDriver};
   authenticator = https://xxx;
   UID = &db_user.;
   PWD = &db_pass.;
   ROLE = ROLE_&sas_user.;
   WAREHOUSE= AAA_BBB_CCC_WH;
   DATABASE = XXX;
  "
;