BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9

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

3 REPLIES 3
FriedEgg
SAS Employee

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;

saspert
Pyrite | Level 9

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.

FriedEgg
SAS Employee

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 755 views
  • 0 likes
  • 2 in conversation