DATA Step, Macro, Functions and more

Default value for Where condition in PROC SQL

Reply
Super Contributor
Posts: 275

Default value for Where condition in PROC SQL

Hi,

I have a coding issue related to PROC SQL. In the code below.

PURCHASER and CUSTOMER are two macro variables whose values are populated by a javascript drop down values which allow only 1 value to be selected.

Example:

PURCHASER: NJ State, TX State, PA State...

CUSTOMER: Dept of labor, Dept of Commerce....

The new filter I am trying to add has a default value of All but is also a drop down menu where 1 value can be selected. The new code is in red below

CSATREPID: All. Jim, John, Joe....

PROC SQL;
   connect to oracle as myconn (user=&ORAID password=&ORAPW path=&ORAPATH );
  CREATE TABLE HIST_ADT AS SELECT  *
   from connection to myconn
(SELECT DISTINCT
    PRCHSR_CD AS Purchaser_Code
    ,CUST_LEG_NM AS Customer_Short_Name
    ,FST_NM AS First_Name
    ,MIDL_NM AS Middle_Name
    ,LST_NM AS Last_Name
....
FROM    D_MEMBER D_MEMBER
INNER JOIN  F_CSAT_OPER F_CSAT_OPER
INNER JOIN  D_CSAT_OPER D_CSAT_OPER   
....

WHERE 
(D_CUSTOMER.PRCHSR_CD = %str(%')&PURCHASER.%str(%')
AND D_CUSTOMER.CUST_LEG_NM = %str(%')&CUSTOMER.%str(%')
....
AND
(
D_CSAT_OPER.CSR_USER_NM like %str(%')&CSATREPID.%str(%')
    OR
D_CSAT_OPER.CSR_USER_NM IN (%oracleQuote(parm=CSATREPID))
)
AND ...)
quit;

%macro oracleQuote(parm=);

%if %symexist(&parm.0) %then %do;

   %do i = 1 %to &&&parm.0;

       %if &i = 1 %then

  %do;

    %str(%')&&&parm&i%str(%')

           %end;

            %else %do;

      %str(,%str(%')&&&parm&i%str(%'))

                %end;

       %end;

       %end;

        %else %do;

  %str(%')&&&parm%str(%')

%end;

%mend;

The issue is - if I select All, the PROC SQL returns 0 records because there are no values for 'All' but in fact should return all records matching Jim, Joe, John et al. Is there a way to covert 'All' option to default to everything?

Thanks,

saspert

Trusted Advisor
Posts: 1,301

Default value for Where condition in PROC SQL

put your sql query into a macro and account for the all condition in a dynamic where clause construction.

for example

%if %upcase(&cust)=%str(ALL) %then

  %do;

   where abc;

  %end;

%else

  %do;

     where cust like %oraquote(parm=&cust);

   %end;

Super Contributor
Posts: 275

Re: Default value for Where condition in PROC SQL

Thanks FriedEgg -do you know if sas macros evaluating it at run time will not be a problem? I am trying it anyways to see for myself. Your logic is very appealing - so I would end up using some thing like this -

%if %upcase(&cust)=%str(ALL) %then

  %do;

   where abc;

  %end;

%else

  %do;

     where abc

     and  cust like %oraquote(parm=&cust);

   %end;

This would add more complexity to the where condition I already have. I mashed up your idea with the existing logic to come up with this -

and

(

case when &CSATREPID = 'All' then D_CSAT_OPER.CSR_USER_NM is not null

else D_CSAT_OPER.CSR_USER_NM IN (%oracleQuote(parm=CSATREPID)

)

I will try them both as soon as I get the data in environment.

Trusted Advisor
Posts: 1,301

Default value for Where condition in PROC SQL

without really knowing your exact usecase scenario I would not forsee there being any issues with the macro code resolution.

Ask a Question
Discussion stats
  • 3 replies
  • 195 views
  • 0 likes
  • 2 in conversation