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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.