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.
Create table tbl1 as
%if &type> ' ' %then AND P.type_C = &type;
Can somone explain me what does this if then and do here.
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.
Thank you, I think I understand it now. The program is pretty long but I will try to give you full details.
value type1 = 'Female' 2 = 'Male' ;
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
%put &type.; It prints 1 for me
select age, type, name, orderdate, code
code in ('5435','4234',786')
%if &type. > ' ' %then AND p.type_c = &type;
%if &minage. > ' ' %then AND TRUNC(months_between(orderdate, BIRTH_DATE) / 12) &minage.;
and line =1
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
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 ""
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*/
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;
The paper @Astounding mentions is a classic, and worth a read:
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;
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.
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.
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.