topic Re: Please help me on how to remove multiple and conditions if condition is failed in SAS Programming

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

Mady8 — Wed, 22 Jul 2020 18:10:59 GMT

%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

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

smantha — Wed, 22 Jul 2020 18:16:35 GMT

%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;

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

FreelanceReinh — Wed, 22 Jul 2020 18:59:31 GMT

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.

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

smantha — Thu, 23 Jul 2020 04:52:54 GMT

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;