topic Re: Default value for Where condition in PROC SQL in SAS Programming

Default value for Where condition in PROC SQL

saspert — Mon, 03 Oct 2011 19:17:01 GMT

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;

%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

Default value for Where condition in PROC SQL

FriedEgg — Mon, 03 Oct 2011 19:27:37 GMT

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;

Re: Default value for Where condition in PROC SQL

saspert — Mon, 03 Oct 2011 20:50:26 GMT

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.

Default value for Where condition in PROC SQL

FriedEgg — Mon, 03 Oct 2011 21:44:19 GMT

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