ERROR: Required operator not found in expression

[Aexor]

Hi ,

Can anyone please help me in the condition check, for a particular condition check using %if I am getting below issue 

%if input(b.rule_val_1526,1.) = 1 %then %do;
input(b.rule_val_1503,best18.) as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
input(b.rule_val_1504,best18.) as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,
%end;
%else %if input(b.rule_val_1526,1.) = 0 or input(b.rule_val_1526,1.) = . %then %do;
. as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
. as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,

ERROR: Required operator not found in expression: input(b.rule_val_1526,1) = 1

What went wrong in the condition check..

the table b is having rule_val_1526 column and data format is char

[PaigeMiller]

ERROR: Required operator not found in expression: input(b.rule_val_1526,1) = 1

The second argument of the INPUT function must be an informat. You do not have an informat as the second argument according to this ERROR message. Perhaps you want this:

input(b.rule_val_1526,1.)

now the second argument is an informat (might not be the right informat, that's up to you). But, code like this will probably never work 

%if input(b.rule_val_1526,1.) = 1 %then %do;

because the %IF function treats the input(b.rule_val_1526,1.) = 1 as text, not as a logical comparison of two values, and that's probably not what you want. In fact, since this piece of code is embedded in PROC SQL, there is no way for a macro %IF to access the values of b.rule_val_1526 to execute this %IF. Perhaps you want the input() function to be in a CASE WHEN rather than in an %IF statement.

[FreelanceReinh]

---

@PaigeMiller wrote:

... the %IF function treats the input(b.rule_val_1526,1.) = 1 as text, not as a logical comparison of two values, ...

---

Just as a side note: I think, technically, the macro processor does try to interpret this %IF condition as a (nonsensical) comparison of two values, but complains about the missing "required operator" on the left-hand side between the word "input" and the text expression "b.rule_val_1526,1." in parentheses. With an inserted operator such as an equal sign or without the parentheses that particular error message would not occur.

[Aexor]

Thanks for suggesting . it's not working like that 😞 too

[Aexor]

thanks, it is giving same error %if input(b.rule_val_1526,1.) = 1 %then %do;
I think I need to post while proc sql query. I got to know that if condition can't be used inside proc sql.

[PaigeMiller]

---

@Aexor wrote:
thanks, it is giving same error %if input(b.rule_val_1526,1.) = 1 %then %do;
I think I need to post while proc sql query. I got to know that if condition can't be used inside proc sql.

---

Perhaps you didn't read the rest of my post. I explained how

%if input(b.rule_val_1526,1.) = 1

cannot work and do what you want because %IF cannot access the values of data set variables such as b.rule_val_1526 and that you need to use CASE WHEN instead of %IF

[Aexor]

proc sql noprint;
create table &abt_worklib..input_plan_rules_1
as
select trim(lowcase(a.pricing_plan_id)) as pricing_plan_id length=37,
trim(lowcase(a.business_category_id)) as business_category_id label='business_category_id' length=37,
&gv_current_dt. as current_date format=&gv_rd_date_sas_format. label='current_date',
%if %length(&init_rec_promo_dt) eq 0 %then %do;
. as initial_rec_date_promo format=&gv_rd_date_sas_format. label='initial_rec_date_promo',
%end;
%else %do;
&init_rec_promo_dt. as initial_rec_date_promo format=&gv_rd_date_sas_format. label='initial_rec_date_promo',
%end;
%if %length(&last_rec_dt_promo) eq 0 %then %do;
. as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo',
%end;
%else %do;
&last_rec_dt_promo. as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo',
%end;
/*2 as opt_flag format=1. length=3,*/
&opt_flag. as opt_flag format=1. length=3,
'' as promo_period length=500 informat=$500. format=$500.,
case when input(b.rule_val_1525,1.) = 0 then 5
when input(b.rule_val_1525,1.) = 1 then 4
when input(b.rule_val_1525,1.) = 2 then 1
else 0 end as obj_func_promo format=3. length=3,
input(b.rule_val_1404,1.) as promo_price_grid_type format=1. length=3,
input(b.rule_val_1301,1.) as is_inventory_constrained format=1. length=3,
input(b.rule_val_1302,best9.) as min_discount_pct_promo format=&gv_rd_pct_sas_format.,
input(b.rule_val_1303,best9.) as max_discount_pct_promo format=&gv_rd_pct_sas_format.,
case when input(b.rule_val_1306,1.) = 1 then input(b.rule_val_1304,best9.)
else . end as min_margin_pct_promo format=&gv_rd_pct_sas_format.,
case when input(b.rule_val_1306,1.) = 2 then input(b.rule_val_1304,best18.)
else . end as min_margin_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1306,1.) = 1 then input(b.rule_val_1305,best9.)
else . end as max_margin_pct_promo format=&gv_rd_pct_sas_format.,
case when input(b.rule_val_1306,1.) = 2 then input(b.rule_val_1305,best18.)
else . end as max_margin_promo format=&gv_rd_amt_sas_format.,
%if input(b.rule_val_1526,1.) = 1 %then %do;
input(b.rule_val_1503,best18.) as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
input(b.rule_val_1504,best18.) as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,
%end;
%else %if input(b.rule_val_1526,1.) = 0 or input(b.rule_val_1526,1.) = . %then %do;
. as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
. as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,
%end;
%if input(b.rule_val_1527,1.) = 1 %then %do;
input(b.rule_val_1505,best12.) as sell_through_units_lb_promo format=&gv_rd_qty_sas_format.,
input(b.rule_val_1506,best12.) as sell_through_units_ub_promo format=&gv_rd_qty_sas_format.,
%end;
%else %if input(b.rule_val_1527,1.) = 0 or input(b.rule_val_1527,1.) = . %then %do;
. as sell_through_units_lb_promo format=&gv_rd_qty_sas_format.,
. as sell_through_units_ub_promo format=&gv_rd_qty_sas_format.,
%end;
%if input(b.rule_val_1528,1.) = 1 %then %do;
input(b.rule_val_1501,best18.) as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
input(b.rule_val_1502,best18.) as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,
%end;
%else %if input(b.rule_val_1528,1.) = 2 %then %do;
input(b.rule_val_1507,best9.) as gross_margin_pct_lb_promo format=&gv_rd_qty_sas_format.,
input(b.rule_val_1508,best9.) as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format.,
%end;
%else %if input(b.rule_val_1528,1.) = 0 or input(b.rule_val_1528,1.) = . %then %do;
. as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
. as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,
. as gross_margin_pct_lb_promo format=&gv_rd_qty_sas_format.,
. as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format.,
%end;
1 as allow_manual_mkd format=1. length=3,
1 as allow_manual_promo_on_reg_price format=1. length=3,
1 as allow_stacked_promo_on_mkd format=1. length=3,
1 as allow_stacked_promo_on_promo format=1. length=3,
1 as allow_promo_price_below_mpp format=1. length=3
/*input(b.rule_val_516,6.) as min_periods_since_avail_dt format=&gv_rd_cnt_sas_format.*/
from &abt_worklib..pricing_plan a

left join &abt_worklib..plan_rule_transposed b
on upcase(a.pricing_plan_id) = upcase(b.pricing_plan_id)

left join &pricing_lib..date_dm e
on a.end_dt between e.fiscal_start_dt and e.fiscal_end_dt
and e.date_lvl_no = &time_low_lvl.
;
quit;
%end;

[Patrick]

What you are principally missing: Anything SAS Macro language will pre-process before your Proc SQL even starts running. 

SAS Macro language is mainly used to dynamically create SAS code. 

What you are trying to do is to define a condition in macro language that uses data from your Proc SQL. But when the macro code executes (which is only textual) the macro processor won't know anything about your SQL variables. It will just execute the macro level logic and the leave some SAS/SQL level code. Then the SAS processor comes and will execute this code.

On top of it: You can't use SAS functions directly on macro level. You do need to instruct the macro processor to call the SAS function via %sysfunc(<SAS function>).

But again: Even you'd wrap a %sysfunc() around the input function there won't be an column b.rule_val_1526 available. On a macro level the macro processor would just treat it as a string.

You need to revamp your logic. If you want the SQL to return values based on some other column then use a SQL CASE statement.

[Kurt_Bremser]

Get rid of all the macro code and use CASE - WHEN - THEN - ELSE - END logic, as this is the method SQL provides for conditional evaluation.

[Aexor]

Thanks. It still giving me wrong input for columns. for.eg I am getting "missing value" for all columns where case statement is applied in 15** series

[Aexor]

proc sql noprint;
create table &abt_worklib..input_plan_rules_1
as
select trim(lowcase(a.pricing_plan_id)) as pricing_plan_id length=37,
trim(lowcase(a.business_category_id)) as business_category_id label='business_category_id' length=37,
&gv_current_dt. as current_date format=&gv_rd_date_sas_format. label='current_date',
%if %length(&init_rec_promo_dt) eq 0 %then %do;
. as initial_rec_date_promo format=&gv_rd_date_sas_format. label='initial_rec_date_promo',
%end;
%else %do;
&init_rec_promo_dt. as initial_rec_date_promo format=&gv_rd_date_sas_format. label='initial_rec_date_promo',
%end;
%if %length(&last_rec_dt_promo) eq 0 %then %do;
. as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo',
%end;
%else %do;
&last_rec_dt_promo. as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo',
%end;
/*2 as opt_flag format=1. length=3,*/
&opt_flag. as opt_flag format=1. length=3,
'' as promo_period length=500 informat=$500. format=$500.,
case when input(b.rule_val_1525,1.) = 0 then 5
when input(b.rule_val_1525,1.) = 1 then 4
when input(b.rule_val_1525,1.) = 2 then 1
else 0 end as obj_func_promo format=3. length=3,
input(b.rule_val_1404,1.) as promo_price_grid_type format=1. length=3,
input(b.rule_val_1301,1.) as is_inventory_constrained format=1. length=3,
input(b.rule_val_1302,best9.) as min_discount_pct_promo format=&gv_rd_pct_sas_format.,
input(b.rule_val_1303,best9.) as max_discount_pct_promo format=&gv_rd_pct_sas_format.,
case when input(b.rule_val_1306,1.) = 1 then input(b.rule_val_1304,best9.)
else . end as min_margin_pct_promo format=&gv_rd_pct_sas_format.,
case when input(b.rule_val_1306,1.) = 2 then input(b.rule_val_1304,best18.)
else . end as min_margin_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1306,1.) = 1 then input(b.rule_val_1305,best9.)
else . end as max_margin_pct_promo format=&gv_rd_pct_sas_format.,
case when input(b.rule_val_1306,1.) = 2 then input(b.rule_val_1305,best18.)
else . end as max_margin_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1526,1.) = 1 then input(b.rule_val_1503,best18.)
else . end as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1526,1.) = 1 then input(b.rule_val_1504,best18.)
else . end as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1526,1.) = 0 or input(b.rule_val_1526,1.) = . then .
else . end as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1526,1.) = 0 or input(b.rule_val_1526,1.) = . then .
else . end as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1527,1.) = 1 then input(b.rule_val_1505,best18.)
else . end as sell_through_units_lb_promo format=&gv_rd_qty_sas_format.,
case when input(b.rule_val_1527,1.) = 1 then input(b.rule_val_1506,best18.)
else . end as sell_through_units_ub_promo format=&gv_rd_qty_sas_format.,
case when input(b.rule_val_1527,1.) = 0 or input(b.rule_val_1527,1.) = . then .
else . end as sell_through_units_lb_promo format=&gv_rd_qty_sas_format.,
case when input(b.rule_val_1527,1.) = 0 or input(b.rule_val_1526,1.) = . then .
else . end as sell_through_units_ub_promo format=&gv_rd_qty_sas_format.,
case when input(b.rule_val_1528,1.) = 1 then input(b.rule_val_1501,best18.)
else . end as gross_margin_lb_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1528,1.) = 1 then input(b.rule_val_1502,best18.)
else . end as gross_margin_ub_promo format=&gv_rd_amt_sas_format.,
case when input(b.rule_val_1528,1.) = 2 then input(b.rule_val_1507,best18.)
else . end as gross_margin_pct_lb_promo format=&gv_rd_qty_sas_format.,
case when input(b.rule_val_1528,1.) = 2 then input(b.rule_val_1508,best18.)
else . end as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format.,
case when input(b.rule_val_1528,1.) = 0 or input(b.rule_val_1528,1.) = . then .
else . end as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format.,
1 as allow_manual_mkd format=1. length=3,
1 as allow_manual_promo_on_reg_price format=1. length=3,
1 as allow_stacked_promo_on_mkd format=1. length=3,
1 as allow_stacked_promo_on_promo format=1. length=3,
1 as allow_promo_price_below_mpp format=1. length=3,
input(b.rule_val_1508,best9.) as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format.
/*input(b.rule_val_516,6.) as min_periods_since_avail_dt format=&gv_rd_cnt_sas_format.*/
from &abt_worklib..pricing_plan a

left join &abt_worklib..plan_rule_transposed b
on upcase(a.pricing_plan_id) = upcase(b.pricing_plan_id)

left join &pricing_lib..date_dm e
on a.end_dt between e.fiscal_start_dt and e.fiscal_end_dt
and e.date_lvl_no = &time_low_lvl.
;
quit;

[Tom]

Clean up some of the other issues and see if it helps.

For example the TRIM() function is not doing anything here:

select trim(lowcase(a.pricing_plan_id)) as pricing_plan_id length=37

TRIM() just removes the trailing spaces and they will be added back when the value is stored so that PRICING_PLAN_ID is padded to its full 37 byte length.

And all of those INPUT() function calls like:

input(b.rule_val_1404,1.)
input(b.rule_val_1304,best9.)

First don't use BEST as if it meant something special as an INFORMAT.  BEST is the name of a FORMAT that tries to find the "best" way to display a number in a fixed number of characters.  But there is no similar concept for making a number from a string.  There is only one way to store a number in SAS.

Then INPUT() function does not care if the WIDTH you use on the INFORMAT specification is larger than the LENGTH of the string being read  So unless there are characters past the first byte of rule_val_1404 or the ninth byte of rule_val_1404 you want to ignore then just use width of 32 which is the maximum that the numeric informat supports. 

Also using short widths on the informats might cause trouble if the character string has leading spaces.  What if you had these strings for those two variables?  Your INPUT() function calls would return missing value as the result.

rule_val_1404 = '     1' ;
rule_val_1304 ='          12';

If you are not sure how messy the character variables are you might want to use LEFT() function to remove the leading spaces before converting them into numbers.

input(left(b.rule_val_1404),32.)
input(left(b.rule_val_1304),32.)

[Tom]

That cannot work as the macro processor will work BEFORE the generated SQL code starts to run.  So the %IF statements cannot base their decision on the values of variables in datasets.

It is not clear what those %IF statements are trying to do but perhaps you can re work them to be more like this block that is NOT trying to reference the values of dataset variables.

%if %length(&last_rec_dt_promo) eq 0 %then %do;
. as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo',
%end;
%else %do;
&last_rec_dt_promo. as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo',
%end;

Put it might be easier to just do:

%if %length(&last_rec_dt_promo) eq 0 %then %do;
  %let last_rec_dt_promo=.;
%end;
&last_rec_dt_promo. as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo',

If you are having trouble then first make SAS code that works without any macro code.  Then you can work on using macro variables and/or macro logic to help generate the SAS code.

If you need more help provide the two input datasets and values for the macro variables referenced. And explain what code you want the macro logic to generate.  And what result you would expect from that code for the given inputs.