topic Re: if then else in a macro in SAS Programming

if then else in a macro

Froebel — Mon, 27 Mar 2023 21:52:05 GMT

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.

Re: if then else in a macro

Astounding — Mon, 27 Mar 2023 22:26:54 GMT

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.

Re: if then else in a macro

ballardw — Tue, 28 Mar 2023 00:10:14 GMT

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*/

Re: if then else in a macro

Froebel — Tue, 28 Mar 2023 04:30:23 GMT

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

Re: if then else in a macro

Froebel — Tue, 28 Mar 2023 04:27:28 GMT

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?

Re: if then else in a macro

Kurt_Bremser — Tue, 28 Mar 2023 06:32:44 GMT

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 ""

Re: if then else in a macro

Astounding — Tue, 28 Mar 2023 12:12:24 GMT

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;

Re: if then else in a macro

Quentin — Tue, 28 Mar 2023 12:38:59 GMT

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;

Re: if then else in a macro

ballardw — Tue, 28 Mar 2023 15:01:29 GMT

@Froebel wrote:
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?

Part of the issue could well be where the macro variables Type and Minage are set. You do not show that anywhere.

Not sure what you are expecting for a result as there is no data accompanying the macro or the expected result. Adding the MINAGE added another syntax issue:

 AND TRUNC(months_between(orderdate, BIRTH_DATE) / 12) &minage.;

I don't find any documentation for a MONTHS_BETWEEN function in SAS. Is that some locally defined function (typically for such I would expect to see the INTCK function). And regardless the use of "And trunc(somevalue) &minage." will be a syntax error.

Suggestion: Turn on the option MPRINT as I showed above. Run your macro. Go to the log and copy the result of running your macro with all the MPINT notes. On the forum open a text box and paste that text. I strongly suspect you will see at least one if not more error messages.

You may have not been bothering to tell us about the errors you are receiving from the code because the default error messages shown after a macro runs are bit difficult to connect with specific statements. The MPRINT option will make the errors appear in closer proximity to the code causing them.

Re: if then else in a macro

Froebel — Tue, 28 Mar 2023 16:39:07 GMT

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.

Re: if then else in a macro

Froebel — Tue, 28 Mar 2023 16:39:21 GMT

Re: if then else in a macro

Astounding — Tue, 28 Mar 2023 16:53:45 GMT

Your program contains this line:

%put &sex;

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

Re: if then else in a macro

Quentin — Tue, 28 Mar 2023 17:05:36 GMT

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.

Re: if then else in a macro

Froebel — Tue, 28 Mar 2023 18:53:29 GMT

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

Re: if then else in a macro

Quentin — Tue, 28 Mar 2023 18:57:32 GMT

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

That would only happen if the value of the macro variable is a dot, rather than being blank. In the macro language, a dot is a value.

Re: if then else in a macro

Froebel — Tue, 28 Mar 2023 19:08:06 GMT

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.

Re: if then else in a macro

Quentin — Tue, 28 Mar 2023 19:14:19 GMT

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.