BookmarkSubscribeRSS Feed
ncsthbell
Quartz | Level 8

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.
7 REPLIES 7
FriedEgg
SAS Employee

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.

ballardw
Super User

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.

Astounding
PROC Star

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.

ncsthbell
Quartz | Level 8

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)

FriedEgg
SAS Employee

You are using &Line1_From_Amt twice instead of &LINE1_TO_AMT

Astounding
PROC Star

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. 

ballardw
Super User

@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.

 

 

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
  • 7 replies
  • 1033 views
  • 1 like
  • 4 in conversation