Hello,
I would like to make create a table using a proc sql but selecting different library based on the type of environment (DEV for development or PROD for production) .
For exemple, if TypeEnv eq DEV , I would like to select the symy library and if TypeEnv eq PROD, I would like to select the 12symu library
I have tried this code and it is not working
%let typeEnv=DEV;
*%let typeEnv=PROD;
%macro Transfert;
/*tranferts par agents ATO (Agents au Traitement des Opportunités);*/
proc sql;
create table transfert
as select table1.quand, table1.cdnname, table1.callsanswered as transferts, table1.callsoffered, table1.callsabandoned,
table1.callsterminated, table2.sannee, table2.smois, table2.trimestre, table2.semaine
%if typeEnv eq DEV %then
%do;
FROM symu.icdnstat as table1 left join commun.dates as table2
ON table1.quand = table2.date;
%end;
%else
%do;
FROM l2symu.icdnstat as table1 left join commun.dates as table2
ON table1.quand = table2.date;
%end;
QUIT;
%mend Transfert;
%Transfert;
Does any one have already found a solution for this situation?
Regards,
Why have a parameter which tells the code which library to look at? That just seems like an unnecessary step. Code you program to look at a library, the only switch needs to be where you assign the library:
libname curr "%sysfunc(pathname(symu))"; /* Replace with this for prod libname curr "%sysfunc(pathname(i2symu))"; */ proc sql; ... from CURR....; quit;
What about creating a macro variable with the correct library reference and using that in the join instead of the conditional logic on the join?
Untested but should give you the idea:
%macro Transfert(env=);
/*tranferts par agents ATO (Agents au Traitement des Opportunités);*/
data _null_;
if "&env"="PROD" then call symputx('mylib', 'symu', 'l');
else if "&env" = "DEV" then call symputx('mylib', 'l2symu', 'l');
run;
proc sql;
create table transfert
as select table1.quand, table1.cdnname, table1.callsanswered as transferts, table1.callsoffered, table1.callsabandoned,
table1.callsterminated, table2.sannee, table2.smois, table2.trimestre, table2.semaine
FROM &mylib..icdnstat as table1
left join commun.dates as table2
ON table1.quand = table2.date;
QUIT;
%mend Transfert;
%Transfert(DEV);
%Transfert(PROD);
Have you tried properly referencing your macro variable? This is never going to match:
%if typeEnv eq DEV %then
%do;
Instead, the comparison should be:
%if &typeEnv eq DEV %then
%do;
Why have a parameter which tells the code which library to look at? That just seems like an unnecessary step. Code you program to look at a library, the only switch needs to be where you assign the library:
libname curr "%sysfunc(pathname(symu))"; /* Replace with this for prod libname curr "%sysfunc(pathname(i2symu))"; */ proc sql; ... from CURR....; quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.