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
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;
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.
without really knowing your exact usecase scenario I would not forsee there being any issues with the macro code resolution.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.