topic Re: Trying to build 'where' clause based on user inputs, getting error and having problems resolving in SAS Programming

Trying to build 'where' clause based on user inputs, getting error and having problems resolving.

ncsthbell — Tue, 08 Mar 2016 22:04:38 GMT

I am building a program which gives the user about 10 different prompts in which they can populate. I am trying to build a 'where' statement based on testing the length of the prompt to see a value was entered. I have just started with testing the first prompt which is a 'from' & 'to' amount. I can't get the first one correct! I am including my code and the error I am getting. Any guidance would be greatlyh appreciated!!

/* Build 'where' clause based on filters entered */
%macro BuildWhereClause;
  %global where_clause;
  %let where_clause = 1=1;

/*- TYPE1_amounts - */
  %if %length((&TYPE1_From_Amt > 0) and (&TYPE1_To_Amt > 0)) %then %do 
      %let  where_clause  = &where_clause and
 (&TYPE1_From_Amt >= TYPE1_LINE1 and & TYPE1_From_Amt <= TYPE1_LINE1);
  %end;
%mend;
%BuildWhereClause;

ERROR: Expected %TO not found in %DO statement.  A dummy macro will be compiled.
125          %end;
126        %mend;
127        %BuildWhereClause;
           _
           180
WARNING: Apparent invocation of macro BUILDWHERECLAUSE not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: Trying to build 'where' clause based on user inputs, getting error and having problems resolving

FriedEgg — Tue, 08 Mar 2016 23:05:36 GMT

From a quick glance, you are missing a semicolon after your do statement. Your logic in the %if statement is also not doing what you probably are wanting it to due to your parenthesis placement.

Re: Trying to build 'where' clause based on user inputs, getting error and having problems resolving

ballardw — Tue, 08 Mar 2016 22:41:19 GMT

Your line of code:

%if %length((&TYPE1_From_Amt > 0) and (&TYPE1_To_Amt > 0))

does not compare as you expect. In effect it says %if 25 %then . The value will never be 0 (false) because it will be counting the parantheses, spaces letters composing "and". I think the smallest value you may get is 17.

If you are trying to compare two variable then you need something more like

%if %eval(%length(&TYPE1_From_Amt) > 0 and %length(&TYPE1_to_Amt) > 0) %then

Look at results for this to see some differences:

%let TYPE1_From_Amt= something;
%let TYPE1_to_Amt= something else;

%let result = %length((&TYPE1_From_Amt > 0) and (&TYPE1_To_Amt > 0)) ;
%put &result;

%let result = %eval(%length(&TYPE1_From_Amt) > 0 and %length(&TYPE1_to_Amt) > 0);
%put &result;

A %do block without a loop requires a ";" after the %do which should clear that error.

%if %eval(%length(&TYPE1_From_Amt) > 0 and %length(&TYPE1_to_Amt) > 0) %then %do;

%let ...

%end;

Also this bit

and & TYPE1_From_Amt <= TYPE1_LINE1

will have likely have an issue because of the space between "&" and "type1"

and corrected

(&TYPE1_From_Amt >= TYPE1_LINE1 and &TYPE1_From_Amt <= TYPE1_LINE1); will only be true for = because you are comparing the same value to be >= and <= the same variable. One of thes might have been meant to be the "to" value

which might be better expressed as (guessing here)

( &Type1_From_Amt <= Type1_Line1 <= &Type1_To_Amt)

Note: you should verify that the actual values of the "from" is <= to the "to" as well before creating any data step code.

Re: Trying to build 'where' clause based on user inputs, getting error and having problems resolving

Astounding — Tue, 08 Mar 2016 23:34:30 GMT

A likely fix:

%if (%length(&type1_From_Amt) > 0) and (%length(&Type1_To_Amt) > 0) %then %do;

Note that this can be simplified to become:

%if %length(&type1_From_amt) and %length(&type1_To_Amt) %then %do;

Also, remove the extra blank later on: & type1_from_amt should become &type1_from_amt

That should get you 90% of the way there, possibly 100%.

Good luck.

Re: Trying to build 'where' clause based on user inputs, getting error and having problems resolving

ncsthbell — Wed, 09 Mar 2016 17:26:09 GMT

I have spent over a day trying to get this to work, getting very frustrated! The report I am building has 15 prompts (user wants) and I can't get the results correct on the first one. The code runs but my 'where clause' results is not correct.

I need to build the 'where clause' based on the prompts they enter. They can leave any of them blank. I appreciate any suggestions.

Here is my code & the result of the 'where statement':

%LET LINE1_FROM_AMT = 16000;
%LET LINE1_TO_AMT = 17000;

%macro BuildWhereClause;

   %global where_clause;
   %let where_clause = &where_clause;
  
   %if %eval(%length(&line1_From_Amt) > 0) and %eval(length(&Line1_To_Amt) > 0) %then %do; 
       %let where_clause = &where_clause and
            (&Line1_From_Amt >= SALES_LINE1 and &Line1_From_Amt <= SALES_LINE1);
   %end;
%mend;
%BuildWhereClause; 

Result:
148        %put WHERE STMT IS:  &where_clause.;
WHERE STMT IS:  and            (16000 >= SALES_LINE1 and 16000 <= SALES_LINE1)

Re: Trying to build 'where' clause based on user inputs, getting error and having problems resolving

FriedEgg — Wed, 09 Mar 2016 17:28:29 GMT

You are using &Line1_From_Amt twice instead of &LINE1_TO_AMT

Re: Trying to build 'where' clause based on user inputs, getting error and having problems resolving

Astounding — Wed, 09 Mar 2016 17:35:19 GMT

The only clearcut error is that you are using &Line1_From_Amt twice, instead of using &Line1_To_Amt in one of the comparisons.

You have a decision to make about what should be in your WHERE clause. Should it include the word WHERE? You originally had 1=1 as part of it. Should that be part of it or not? (I'm not sure it would work, but that's still a decision you have to make.)

Based on some of your decisions, you may need to check whether this is the first set of prompts that the user has answered. For example, you may need to code something like:

%if %length(&where_clause) > 0 %then %let &where_clause = &where_clause and;

That way, you don't have to add add "and" at the beginning before there are any conditions.

Re: Trying to build 'where' clause based on user inputs, getting error and having problems resolving

ballardw — Wed, 09 Mar 2016 20:43:28 GMT

@ncsthbell wrote:

I have spent over a day trying to get this to work, getting very frustrated! The report I am building has 15 prompts (user wants) and I can't get the results correct on the first one. The code runs but my 'where clause' results is not correct.

I need to build the 'where clause' based on the prompts they enter. They can leave any of them blank. I appreciate any suggestions.

Here is my code & the result of the 'where statement':
%LET LINE1_FROM_AMT = 16000;
%LET LINE1_TO_AMT = 17000;

%macro BuildWhereClause;

   %global where_clause;
   %let where_clause = &where_clause;
  
   %if %eval(%length(&line1_From_Amt) > 0) and %eval(length(&Line1_To_Amt) > 0) %then %do; 
       %let where_clause = &where_clause and
            (&Line1_From_Amt >= SALES_LINE1 and &Line1_From_Amt <= SALES_LINE1);
   %end;
%mend;
%BuildWhereClause; 

Result:
148        %put WHERE STMT IS:  &where_clause.;
WHERE STMT IS:  and            (16000 >= SALES_LINE1 and 16000 <= SALES_LINE1)

I get macro errors running this version of your code. Even after fixing the missing % in the second Length call.

I think that you mean to use

%eval(%length(&LINE1_FROM_AMT) > 0 and %length(&LINE1_TO_AMT) > 0)

to evalue the "and". Your code has AND between to evaluated strings.

also Using LENGTH is kind of inefficient. Here is a short utilitiy macro, I can't remember who I got this from, that returns 1 if a value is blank (unassigned value) and 0 other wise:

%macro isBlank(param);
%sysevalf(%superq(param)=,boolean)
%mend isBlank;

Use for example

%put %isblank(&line1_From_Amt);

and in your context:

%if %eval(%isblank(&line1_From_Amt) + %isblank(&line1_To_Amt)) = 0 %then ...

(sum of 0 means both are not blank, sumeof 1 or 2 means one or both are blank)

If you use a macro variable that is not currently defined you will get a warning of symbolic reference not resolved but will return 0.