if then else in a macro

[Froebel]

Hello SAS users

I am working on translating a code written by someone else and need help in understanding the syntax as it is not working as expected.

The code is using if then else statement in the macro with a where condition on the type which holds 1 or 2 value.

Macro Start

Create table tbl1 as

(select 

where 

  %if &type> ' ' %then AND P.type_C = &type;

);

Quit;

Macro End

Can somone explain me what does this if then and do here.

[Astounding]

It's not clear whether the program is correct or not, but I can tell you what it does.  It is considering whether or not to add this to the SAS code:

AND P.type_C = &type

So you need to now the value of &TYPE to determine what happens.

If the program is intending to check whether &TYPE has been assigned any value at all, it is incorrect.  Checking for >' ' is not the way to do it.  But it may be correct, it's just not possible to tell the intention of the programmer.

[Froebel]

Thank you, I think I understand it now. The program is pretty long but I will try to give you full details.

proc format;
value type1 = 'Female' 2 = 'Male' ;
run;

Here is my program, it is using an excel which has value 1 in the Type Field and min age is also populated with value like >20

macro master;

%put &type.;   It prints 1 for me

select age, type, name, orderdate, code

from 

table P

where 

code in ('5435','4234',786') 

and

  %if &type. > ' ' %then AND p.type_c = &type;

%if &minage. > ' ' %then AND TRUNC(months_between(orderdate, BIRTH_DATE) / 12) &minage.;

and line =1

);

quit;

%mend master;

However this code errors out when there is no value in the type field ..in the error message it prints missing expression and it prints p.type_c = .

Like you both mentioned the  >'  ' is not working as expected.

Type only has value 1 or 2 or blank

[Kurt_Bremser]

Your conditional codes in the %IF%THEN all start with an AND, but right before them you already have an unconditional AND, so yout get a double AND, which is invalid syntax.

And the check for a non-empty type should be

%if "&type." ne ""

[ballardw]

It is not a good idea to ask questions about "what does this do" without providing

1) ALL of the code in the step in question

and when there is a comment like "not working as expected." provide what is expected. Which may require providing data, and in the case of macros and example of how you actually called the macro.

I strongly  suspect that you may have left out something important when you removed stuff like the variables selected and the From, like most of the WHERE clause. 

If I had to make a bet what the original programmer intended, given a very incomplete code example, I would say this is supposed to add a condition to the WHERE clause then the TYPE parameter is not blank. As shown it will generate errors because there would be a partial clause that is syntactically incorrect. You can't have an AND without something else
"where and P.type=<whatever type resolves to>" as well as the error that there is not alias P defined in the From.

Actual working example using similar code. This writes to the results window for examination of results.

%macro dummy( skip);
   proc sql;
      select * 
      from sashelp.class
      where sex='F'
        %if &skip > ' ' %then And age>14;
      ;
   quit; 
%mend;

/* select all the Female observations*/
%dummy()  ;

/* select all the Female observations with age > 14*/
%dummy (A);

HOWEVER this is a logic error. Because it is actually comparing the value of TYPE to the character single quote and if type has a value like space, !, #, $ or % (and a few others) then type is not missing and the filter will not be applied.

My example above works because A is "greater than", meaning after, the single quote in the collating sequence of characters.

Note: SAS Macros start with a %macro statement and finish with %Mend; When asking about a macro behavior everything between the %macro and %mend should be provided.

You can see what the code generated by you macro is by using the option Mprint before executing the macro:

options mprint;

%nameofmacro(<parameters to the macro)

options nomprint; /* turn of the macro print*/

[Froebel]

Thank you, I appreciate your response and I realize i should have provided more details. I have added the required code snippet, like you noted there is an error in the part where i am checking for null condition but what is it?

[Astounding]

If you are certain that the code is trying to test for a null value in &TYPE, there are a few ways to do it.  An entire paper has been written on the subject.  But the chosen method is not a valid way:

%if &type. > ' ' %then AND p.type_c = &type;

This method is not correct, because macro language treats quotes as text in that statement.  So the test is whether &type is greater than a quote (or greater than two quotes).  The method I usually choose (and reasonable people can select alternatives) is:

%if %length(&type.) > 0 %then AND p.type_c = &type;

[Quentin]

The paper @Astounding mentions is a classic, and worth a read:

https://support.sas.com/resources/papers/proceedings09/022-2009.pdf

The authors recommend:

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

So your code would be:

%if NOT %isblank(type) %then AND p.type_c = &type;

[Froebel]

Thank you for your response but all the values are bringing the same value for isblank(type). If type is 1 ,2 or . the isblank is bringing 0 for it. So I cant use this for checking non null values. It is storing the null with a . and thats causing all the confusion.

[Quentin]

Yes, if your macro variable is storing a dot instead of being null, that is the problem.  It's not actually null / blank.

Can you change that?  

If you can't then you can change your %IF statement to check if the value of the macro variable is a dot, e.g.:

%if &type ne . %then ... ;

That will do a character comparison to test where the macro variable TYPE has the value dot.

[Froebel]

Hi

I tried your version

%macro dummy;
%DO COUNTER = 1 %TO 3;
DATA Temp;
SET CTRL;
IF ObsNo = &COUNTER;
call symputx ('sex',sex);
call symputx ('labels',compress(labels));
RUN;
%put &sex.;
proc sql;
select *
from sashelp.class
where age = 14
%if %length(&sex.) > 0 %then AND name = 'Henry';
;
quit;
%end;
%mend;
%dummy;



I uploaded the excel in the ctrl sas table and then I am using the sex & label columns to pass it as a variable in the sql. Sex has value 1 in the first row , 2 in the second and blank in the third. I was expecting the third row to bring all results since it is not greater than 0 but it brings only Henry as its output.

[Quentin]

Your dummy code works for me. 

data ctrl ;
  input ObSNo Sex $1. ;
  cards ;
1 M
2 F
3 .
;
run ;

%macro dummy;
%DO COUNTER = 1 %TO 3;
DATA Temp;
SET CTRL;
IF ObsNo = &COUNTER;
call symputx ('sex',sex);
RUN;
%put &sex.;
proc sql;
select *
from sashelp.class
where age = 14
%if %length(&sex.) > 0 %then AND name = 'Henry';
;
quit;
%end;
%mend;
%dummy;

If you have a global macro variable named sex, that could be part of the problem. Your dummy macro will be safer if you add a %LOCAL statement:

%local sex counter ;

Also make sure there is a record in your CTRL data with ObsNo=3.  If there is no record with ObsNo=3, then when &Counter=3 the macro variable SEX will still have the value assigned from &Counter=2.  To avoid this, you could initialize SEX to blank at the top of the %do loop:

%DO COUNTER = 1 %TO 3;
  %let sex= ; 
  *... ;

If you turn on options MPRINT SYMBOLGEN it should make it easier to trace what is happening in the log.

[Froebel]

[Astounding]

Your program contains this line:

%put &sex;

You will need to show us the results of that %PUT statement, from the log.

[Froebel]

I checked the length and it is bringing 1 for all three value , 1 , 2 and .