topic Re: proc sql from tablex based on the value of a macro variable in SAS Enterprise Guide

proc sql from tablex based on the value of a macro variable

alepage — Tue, 12 Dec 2017 17:00:23 GMT

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,

Re: proc sql from tablex based on the value of a macro variable

Reeza — Tue, 12 Dec 2017 17:05:44 GMT

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

Re: proc sql from tablex based on the value of a macro variable

Astounding — Tue, 12 Dec 2017 17:17:48 GMT

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;

Re: proc sql from tablex based on the value of a macro variable

RW9 — Tue, 12 Dec 2017 17:38:19 GMT

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;