Please help me on how to remove multiple and conditions if condition is failed

[Mady8]

%let CLIENT_NAME =FUNANCING;
%LET CLIENT_PL ='Not Selected';
%let SITE_NAME =Altamonte Springs;
%LET QUEUE_NAME ='Not Selected';

%macro clientrun;

data Actual_data;
set change;
where
%if &CLIENT_NAME. ^='Not Selected' %then %do;
CLIENT_NAME in ("&CLIENT_NAME.")
%end;
and
%if &SITE_NAME. ^='Not Selected' %then %do;
SITE_NAME in ("&SITE_NAME.")
%end;
and
%if &CLIENT_PL.^='Not Selected' %then %do;
upcase(CLIENT_PL) in ("&CLIENT_PL.")
%end;
and
%if &QUEUE_NAME. ^='Not Selected' %then %do;
upcase(QUEUE_NAME) in ("&QUEUE_NAME.")
%end;
and COE in ("Credit bureau","Dispute and Support")
;
run;
%mend;

%clientrun;

when I ran this code got the below error::

%let CLIENT_NAME =FUNANCING;
1141 %LET CLIENT_PL ='Not Selected';
1142 %let SITE_NAME =Altamonte Springs;
1143 %LET QUEUE_NAME ='Not Selected';
1144
1145 %macro clientrun;
1146
1147 data Actual_data;
1148 set change;
1149 where
1150 %if &CLIENT_NAME. ^='Not Selected' %then %do;
1151 CLIENT_NAME in ("&CLIENT_NAME.")
1152 %end;
1153 and
1154 %if &SITE_NAME. ^='Not Selected' %then %do;
1155 SITE_NAME in ("&SITE_NAME.")
1156 %end;
1157 and
1158 %if &CLIENT_PL.^='Not Selected' %then %do;
1159 upcase(CLIENT_PL) in ("&CLIENT_PL.")
1160 %end;
1161 and
1162 %if &QUEUE_NAME. ^='Not Selected' %then %do;
1163 upcase(QUEUE_NAME) in ("&QUEUE_NAME.")
1164 %end;
1165 and COE in ("Credit bureau","Dispute and Support")
1166 ;
1167 run;
1168 %mend;
1169
1170 %clientrun;
MLOGIC(CLIENTRUN): Beginning execution.
MPRINT(CLIENTRUN): data Actual_data;
MPRINT(CLIENTRUN): set change;
SYMBOLGEN: Macro variable CLIENT_NAME resolves to FUNANCING
MLOGIC(CLIENTRUN): %IF condition &CLIENT_NAME. ^='Not Selected' is TRUE
SYMBOLGEN: Macro variable CLIENT_NAME resolves to FUNANCING
SYMBOLGEN: Macro variable SITE_NAME resolves to Altamonte Springs
MLOGIC(CLIENTRUN): %IF condition &SITE_NAME. ^='Not Selected' is TRUE
SYMBOLGEN: Macro variable SITE_NAME resolves to Altamonte Springs
SYMBOLGEN: Macro variable CLIENT_PL resolves to 'Not Selected'
MLOGIC(CLIENTRUN): %IF condition &CLIENT_PL.^='Not Selected' is FALSE
SYMBOLGEN: Macro variable QUEUE_NAME resolves to 'Not Selected'
MLOGIC(CLIENTRUN): %IF condition &QUEUE_NAME. ^='Not Selected' is FALSE
MPRINT(CLIENTRUN): where CLIENT_NAME in ("FUNANCING") and SITE_NAME in ("Altamonte Springs")
and and and COE in ("Credit bureau","Dispute and Support") ;
ERROR: Variable and is not on file WORK.CHANGE.
MPRINT(CLIENTRUN): run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ACTUAL_DATA may be incomplete. When this step was stopped there
were 0 observations and 284 variables.
WARNING: Data set WORK.ACTUAL_DATA was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MLOGIC(CLIENTRUN): Ending execution.

As you can see if condition is failed AND operator is keeps on adding. How to deal with it please help me

[smantha]

%let CLIENT_NAME =FUNANCING;
%LET CLIENT_PL ='Not Selected';
%let SITE_NAME =Altamonte Springs;
%LET QUEUE_NAME ='Not Selected';

%macro clientrun;

data Actual_data;
set change;
where
%if &CLIENT_NAME. ^='Not Selected' %then %do;
CLIENT_NAME in ("&CLIENT_NAME.")
%end;
%else %do;
1=1
%end;
and
%if &SITE_NAME. ^='Not Selected' %then %do;
SITE_NAME in ("&SITE_NAME.")
%end;
%else %do;
1=1
%end;
and
%if &CLIENT_PL.^='Not Selected' %then %do;
upcase(CLIENT_PL) in ("&CLIENT_PL.")
%end;
%else %do;
1=1
%end;
and
%if &QUEUE_NAME. ^='Not Selected' %then %do;
upcase(QUEUE_NAME) in ("&QUEUE_NAME.")
%end;
%else %do;
1=1
%end;
and COE in ("Credit bureau","Dispute and Support")
;
run;
%mend;

%clientrun;

[FreelanceReinh]

Hi @Mady8,

You can simply move all ANDs into their preceding %DO-%END block. Thanks to the final, unconditional Boolean expression ("COE in ...") you'll always get a valid WHERE statement.

[smantha · Accepted solution]

Another version

%let CLIENT_NAME =FUNANCING;
%LET CLIENT_PL ='Not Selected';
%let SITE_NAME =Altamonte Springs;
%LET QUEUE_NAME ='Not Selected';
%macro clientrun;
data Actual_data;
set change;
where
%if &CLIENT_NAME. ^='Not Selected' %then %do;
CLIENT_NAME in ("&CLIENT_NAME.") and
%end;
%if &SITE_NAME. ^='Not Selected' %then %do;
SITE_NAME in ("&SITE_NAME.") and
%end;
%if &CLIENT_PL.^='Not Selected' %then %do;
upcase(CLIENT_PL) in ("&CLIENT_PL.") and
%end;
%if &QUEUE_NAME. ^='Not Selected' %then %do;
upcase(QUEUE_NAME) in ("&QUEUE_NAME.") and
%end;
COE in ("Credit bureau","Dispute and Support")
;
run;
%mend;
%clientrun;