Conditional PROC SQL on SAS

user_10853071 0
Viewed 47

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?

sas sql
2 Answers

Two good ways to do this, I think.

First: the easy hack.

%let COD_COC =(52624, 52568, 52572);
%let COD_BLOC = (and t1.cd_bloc in (...));
%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)
 &COD_BLOC <<<<<<< facultative filter    
;quit;

Tada, now it is just ignored. Comment out or delete the second line if you want it to be used (and put values in the ... ).

Second, the more proper way, is to use the macro language. This is more commonly done in a macro, but in 9.4m7 (the newest release, and a few years old now) you can do this in open code.

%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)
%if %sysevalf(%superq(COD_BLOC) ne %nrstr(%(%)),boolean) %then %do;
and (t1.cd_bloc in &COD_BLOC) <<<<<<< facultative filter  
%end;  
;quit;

You have to be careful with the ne () bit because () are macro language syntax elements, hence the long %nrstr to make sure they're properly considered characters. (%str would be okay too, I just default to %nrstr.)

user_16230070

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()
))
...
user_12499620
Related