Execute proc sql based on value of global macro variable

[PegaZeus]

I'm trying to execute some Proc SQL conditionally, based on the value of a global macro variable (&pen_ts). I attempted to do it with call execute () but it tells me the quoted string is too long. This is what I'm trying to do:

data _null_;
  length longstr $ 200;
  longstr = "proc sql;
     create table risk_assess as 
     select distinct compound_id, 
                     code, 
                     penalty_id,
                     track, 
                     min(seq) format=4. as min_of_seq 
                     
     from ps.tent_buy
     where code = '02603026'
     group by compound_id, code, penalty_id, track
     having seq = min(seq);
   quit;";
  if &pen_ts = 'TS' then call execute (longstr);
run;

Any suggestions? Thank you...

[yabwon · Accepted solution]

Hi,

1) why not to use macro, it seems to be easier to maintain:

%macro doSQL()
%if &pen_ts = 'TS' %then 
  %do;
    proc sql;
     create table risk_assess as 
     select distinct compound_id, 
                     code, 
                     penalty_id,
                     track, 
                     min(seq) format=4. as min_of_seq 
                     
     from ps.tent_buy
     where code = '02603026'
     group by compound_id, code, penalty_id, track
     having seq = min(seq);
    quit;
  %end;
%mend doSQL;

%doSQL()

2) Does macrovariable pen_ts contains `'TS'` (with single quotes) as the value or `TS` (without quotes) as the value? This is important to check the %if condition 

since for macrolanguage text `'TS'` not equals to text `TS`

All the best

Bart

[Quentin]

Hi,

The message about quoted string too long is just a warning.  You can turn it off with system option noquotelenmax.

That said, if your goal is to execute a PROC SQL step (or any step) conditionally based on the value of a global macro variable, typically you would use a macro %IF statement for that, e.g.:

%if &pen_ts=TS %then %do ;  %*I am assuming your macro variable does not have quotes in the value;
  proc sql;
       create table risk_assess as 
       select distinct compound_id, 
                       code, 
                       penalty_id,
                       track, 
                       min(seq) format=4. as min_of_seq 
                     
       from ps.tent_buy
       where code = '02603026'
       group by compound_id, code, penalty_id, track
       having seq = min(seq);
     quit;
%end ;

If you're not on a recent version of SAS, you will get an error about %if not valid in open code.  If that happens, you can create a macro, which is probably a good idea anyway, as you can trade the global macro variable for a local macro parameter:

%macro runquery(pen_ts=);
%if &pen_ts=TS %then %do ;  %*I am assuming your macro variable does not have quotes in the value;
  proc sql;
       create table risk_assess as 
       select distinct compound_id, 
                       code, 
                       penalty_id,
                       track, 
                       min(seq) format=4. as min_of_seq 
                     
       from ps.tent_buy
       where code = '02603026'
       group by compound_id, code, penalty_id, track
       having seq = min(seq);
     quit;
%end ;
%mend runquery;

%runquery(pen_ts=no) /*will not execute SQL*/
%runquery(pen_ts=TS) /*will execute SQL*/
%runquery(pen_ts=&pen_ts) /*will pass the value of global macro var to the parameter*/

Once you have written that macro, you'll start seeing additional parameters that might be useful (name of table queried, name of table to create, code ...)