Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

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

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,


Accepted Solutions
Solution
‎12-12-2017 12:46 PM
Super User
Super User
Posts: 9,209

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

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;

View solution in original post


All Replies
Super User
Posts: 22,844

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

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);
Super User
Posts: 6,537

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

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;

Solution
‎12-12-2017 12:46 PM
Super User
Super User
Posts: 9,209

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 246 views
  • 5 likes
  • 4 in conversation