Conditional PROC SQL on SAS

Question

I have a SAS Script containing multiple PROC SQLs. The question is that the SQL Query should be "adapted" based on a SAS variable value, for example :

%let COD_COC =(52624, 52568, 52572);
%let COD_BLOC = ();

proc sql; 
create table work.abordados as 
    select t1.cd_acao, 
    t1.cd_bloc, 
    t1.cd_tip_cnl, 
    t1.cd_cmco, 
    t1.cd_cli, 
    datepart(t1.ts_ctt) format=date9. as data_abordagem,
    intnx('day',datepart(t1.ts_ctt), &Prazo_Compra) format=date9. as data_limite
from db2coc.ctt_cli_cmco t1
where (t1.cd_acao in &COD_COC)
and (t1.cd_bloc in &COD_BLOC) <<<<<<< facultative filter    
;quit;

The question is that the second filter (t1.cd_bloc in &COD_BLOC) should be applied only if the %let COD_BLOC = (); is different of "()".

I´ve been reading about "match/case" on SQL but as far as I know, this test applies to results of queries/values. On my case, what I must test is the SAS variable.

How handle this?

Answer 1

Knowing you want to apply the COD_BLOC in-list filter only when there are one or more values, AND that a proper in-list will have at least 3 source code characters (*), you can test the length as the criteria for using the macro variable.

When the %IF is in open code you need a %do %end block as follows:

...
%if %length(&COD_BLOC) > 2 %then %do;
  and t1.cd in &COD_BLOC
%end;

...

When the code is inside a macro, you can use the above or the below

...
%if %length(&COD_BLOC) > 2 %then
and t1.cd in &COD_BLOC
;
...

Another possible coding solution is to use %sysfunc(IFC(...)) to conditionally generate code

...
%sysfunc(ifc(%length(&COD_BLOC) > 2
  , and t1.cd in &COD_BLOC
  , %str()
))
...