%local question in my code

[nbonda]

options mlogic mprint symbolgen;
data sample_name;
   input id name $;
   cards ;
   1 DAVID
   2 JOHN
   3 JOSH
   ;
run;


%macro mcr_test(dst_term);
%local nm;
proc sql;
select DISTINCT name
into :nm
from sample_name
where %upcase(name)=%upcase("&dst_term");
quit;
%put &nm;
data _null_;   
%if &dst_term=&nm %then %do;
 %put "step1";
%end;
%else %do;
 %put "step2";
%end;
run;
%mend;
%mcr_test(DAVID);
%mcr_test(ANDY);

I wrote this macro to print "step 1" when passing parameter is in sample_name dataset, if not print "step 2". if we can write it efficiently I take suggestions.

Above code running fine when I added %local nm; to macro.  without %local I am getting below error.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: &dst_term=&nm
ERROR: The macro MCR_TEST will stop executing.
MLOGIC(MCR_TEST):  Ending execution..

Anybody explain the difference and mechanism of SAS process.

Thank you.

[PaigeMiller]

Run the program again without the %LOCAL statement. Show us the SASLOG. Click on the {i} icon and paste the SASLOG into that window.

[nbonda]

2    options mlogic mprint symbolgen;
3    data sample_name;
4       input id name $;
5       cards ;

NOTE: The data set WORK.SAMPLE_NAME has 3 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.04 seconds


9       ;
10   run;
11
12
13   %macro mcr_test(dst_term);
14   /*%local nm;*/
15   proc sql;
16   select DISTINCT name
17   into :nm
18   from sample_name
19   where %upcase(name)=%upcase("&dst_term");
20   quit;
21   %put &nm;
22   data _null_;
23   %if &dst_term=&nm %then %do;
24    %put "step1";
25   %end;
26   %else %do;
27    %put "step2";
28   %end;
29   run;
30   %mend;
31   %mcr_test(DAVID);
MLOGIC(MCR_TEST):  Beginning execution.
MLOGIC(MCR_TEST):  Parameter DST_TERM has value DAVID
MPRINT(MCR_TEST):   proc sql;
SYMBOLGEN:  Macro variable DST_TERM resolves to DAVID
MPRINT(MCR_TEST):   select DISTINCT name into :nm from sample_name where NAME="DAVID";
NOTE: Writing HTML Body file: sashtml.htm
MPRINT(MCR_TEST):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.71 seconds
      cpu time            0.46 seconds


MLOGIC(MCR_TEST):  %PUT &nm
SYMBOLGEN:  Macro variable NM resolves to DAVID
DAVID
MPRINT(MCR_TEST):   data _null_;
SYMBOLGEN:  Macro variable DST_TERM resolves to DAVID
SYMBOLGEN:  Macro variable NM resolves to DAVID
MLOGIC(MCR_TEST):  %IF condition &dst_term=&nm is TRUE
MLOGIC(MCR_TEST):  %PUT "step1"
"step1"
MPRINT(MCR_TEST):   run;

NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds


MLOGIC(MCR_TEST):  Ending execution.
32   %mcr_test(ANDY);
MLOGIC(MCR_TEST):  Beginning execution.
MLOGIC(MCR_TEST):  Parameter DST_TERM has value ANDY
MPRINT(MCR_TEST):   proc sql;
SYMBOLGEN:  Macro variable DST_TERM resolves to ANDY
MPRINT(MCR_TEST):   select DISTINCT name into :nm from sample_name where NAME="ANDY";
NOTE: No rows were selected.
MPRINT(MCR_TEST):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


MLOGIC(MCR_TEST):  %PUT &nm
WARNING: Apparent symbolic reference NM not resolved.
&nm
MPRINT(MCR_TEST):   data _null_;
SYMBOLGEN:  Macro variable DST_TERM resolves to ANDY
WARNING: Apparent symbolic reference NM not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: &dst_term=&nm
ERROR: The macro MCR_TEST will stop executing.
MLOGIC(MCR_TEST):  Ending execution.

hi Paige Miller,

log inserted.

[Tom · Accepted solution]

When you use the INTO clause to try to create a macro variable, but the query does not return any observations the macro variable is NOT created.  But when you have a %LOCAL statement to define a macro variable it is defined immediately.

You do not need to run a macro to see that behavior.

85   proc sql noprint;
86    select name into :name
87    from sashelp.class
88    where name = 'sandy'
89    ;
NOTE: No rows were selected.
90   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.14 seconds
      cpu time            0.01 seconds


WARNING: Apparent symbolic reference NAME not resolved.
91   %put name=&name;
name=&name

[Ksharp]

You messed up the macro language and data step code.

options mlogic mprint symbolgen;
data sample_name;
   input id name $;
   cards ;
   1 DAVID
   2 JOHN
   3 JOSH
   ;
run;


%macro mcr_test(dst_term);
%local nm;
proc sql;
select DISTINCT name
into :nm
from sample_name
where upcase(name)="%upcase(&dst_term)";
quit;
%put &nm;
data _null_;   
%if &dst_term=&nm %then %do;
 %put "step1";
%end;
%else %do;
 %put "step2";
%end;
run;
%mend;
%mcr_test(DAVID)
%mcr_test(ANDY)

[nbonda]

Thank you. I was under impression that a function should be macro function within macro. Now I understand where to use macro function.

[FreelanceReinh]

---

@nbonda wrote: 

(...)

 ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: &dst_term=&nm
ERROR: The macro MCR_TEST will stop executing.
MLOGIC(MCR_TEST):  Ending execution..

Anybody explain the difference and mechanism of SAS process.

Thank you.

---

Let's walk through the two executions of your macro:

Macro MCR_TEST is called with parameter value DAVID:

1. The %LOCAL statement creates a macro variable NM in the macro's local symbol table.
2. Macro language elements (recognized by leading % or & signs) in the PROC SQL step are resolved: %upcase(name) resolves to NAME (and not to anything involving the values in dataset SAMPLE_NAME -- see how Ksharp corrected this) and %upcase("&dst_term") resolves to "DAVID" (including the double quotes).
3. Now that the macro processor has resolved these macro language elements, the PROC SQL step is compiled and executed, which populates macro variable NM with the value DAVID.
4. The %PUT statement is executed: The value of NM (i.e. DAVID) is written to the log.
5. Similarly to step 2, macro language in the DATA step is resolved: Both macro variable references in the %IF condition resolve to DAVID, i.e., the condition is met. Hence, only the first %PUT statement is executed: "step1" (including unnecessary double quotes) is written to the log.
6. The DATA step is compiled and executed. After the macro processor has finished its work (see step 5) this is an empty DATA step (data _null_; run;), so this is useless.

Without the %LOCAL statement the PROC SQL step creates the local macro variable NM (assuming that no global macro variable NM exists). No change in steps 2 - 6.

Macro MCR_TEST is called with parameter value ANDY:

1. as above.
2. as above, except that this time %upcase("&dst_term") resolves to "ANDY".
3. The PROC SQL step is compiled and executed, but this time macro variable NM remains empty because no row of dataset SAMPLE_NAME meets the condition NAME="ANDY".
4. The value of NM (i.e. a null string) is written to the log.
5. Macro language in the DATA step is resolved: The condition that the string ANDY is equal to the null string is false. Hence, only the second %PUT statement is executed: "step2" (including unnecessary double quotes) is written to the log.
6. as above.

Without the %LOCAL statement macro variable NM does not exist because the PROC SQL step (selecting no rows) doesn't create it (as Tom has pointed out already). As a consequence, the macro variable references &nm in the %PUT statement and in the %IF condition cannot be resolved (causing two identical warnings in the log: WARNING: Apparent symbolic reference NM not resolved.). In step 4 the %PUT statement writes the unresolved text &nm to the log.

Even worse, the %IF condition resolves to ANDY=&nm, which the macro processor tries to interpret as a compound logical expression consisting of

• the expression ANDY= (saying "the string ANDY equals the null string")
• the expression nm

joined by the Boolean operator AND (written as &).

While ANDY= is a valid logical expression and evaluates to (the Boolean value) FALSE, the string nm is not valid in this context. An integer number (or another logical expression for that matter) in place of nm would be valid (e.g. ANDY=&5) because non-zero integers are evaluated to TRUE and 0 to FALSE. So, it is the string nm which the error message refers to as a "character operand ... where a numeric operand is required." Due to this error, the macro stops executing in the middle of processing the macro language elements in the DATA step code. Finally, the DATA _NULL_ statement, but not the RUN statement (which had not been reached yet) is executed, leaving the SAS session in the state "DATA STEP running".

---

@nbonda wrote:

I wrote this macro to print "step 1" when passing parameter is in sample_name dataset, if not print "step 2". if we can write it efficiently I take suggestions.

---

The second part of the macro code (between %put nm; and %mend;) could be simplified to

%if &dst_term=&nm %then %put step1;
%else %put step2;

No DATA step is required (it would be empty, see above), no %DO-%END blocks (there's only one statement in each block) and no quotation marks around the strings to be written to the log (the macro language would treat them as additional text). However, the %IF condition is still case-sensitive. So, depending on what you want to achieve, you may want to apply the %UPCASE function in the %IF condition or make sure that macro variables DST_TERM and NM contain only upper-case strings at this point (e.g. write
%let dst_term=%upcase(&dst_term); at the beginning of the macro and use the UPCASE function in the SELECT statement).

The correction of the WHERE clause has been mentioned already. The DISTINCT keyword is not mandatory for the INTO clause (which in any case writes only the first selected value into the macro variable), but has the (intended?) side effect that multiple selected values, if any, are sorted so that DAVID would always precede David or david (assuming the ASCII collating sequence) and hence the upper-case value would be written to macro variable NM, regardless of the order of observations in dataset SAMPLE_NAME.

[nbonda]

thank you

[Tom]

You can also use the automatic macro variable SQLOBS to check if your query returned any observations.

Also be careful to distinguish between where you want macro functions (to test text and generate code) and actual SAS functions and SAS statements.  For example you were using macro function %UPCASE() to turn the string name into the string NAME instead of the SAS function UPCASE() to operate on the value of the variable named NAME.  You enclosed a block of pure macro code inside of a data _null_ step for no reason since the macro code did not generate any statements that would run in that data step.

%macro mcr_test(dst_term);
%local nm n;
proc sql noprint;
  select name
    into :nm trimmed
  from sample_name
  where upcase(name)=%upcase("&dst_term")
  ;
%let n=&sqlobs;
quit;
data _null_;
  %if &n %then %do;
     put "Found name=&nm";
  %end;
  %else %do;
    put "Did not find name= &dst_term";
  %end;
run;
%mend;
%mcr_test(David);
%mcr_test(ANDY);

 Results:

160  %mcr_test(David);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



Found name=DAVID
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds


161  %mcr_test(ANDY);
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



Did not find name= ANDY
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds