How to mask "OR" with variable list passed through using SYSPBUFF in macro

Question

I'm using SYSPBUFF to pass through various numbers of parameters into a macro. Specifically, I am passing through a list of states. One of the states being used is Oregon or "OR" and that one state is causing me error.

I get the error "ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &ST^= ERROR: The condition in the %DO %WHILE loop, &ST^=, yielded an invalid or missing value, . The macro will stop executing.

I've used all the various quoting masks to try to resolve this issue but none of it is working.

&STATES includes the following states: AK,AZ,CA,HI,ID,NV,OR,WA

Here is my current code:

RSUBMIT;
PROC SQL;
connect to oracle
(path=DW user=&USER pw=&PW);
%macro DTCNT() / parmbuff; 
%let i=1;
%let ST=%scan(&SYSPBUFF,&I);  

%do %while (&ST^=);
CREATE TABLE MD_&ST._IP_ADJDT_CNTS_S1 AS
select *
from connection to oracle
  (SELECT adjudication_date,
          count (*) as LINE_CNT 
   from MD_r&NUM..&ST._IP_hdr_f
   group by adjudication_date
   order by adjudication_date);

      %let i=%eval(&I+1);  
      %let ST=%scan(&SYSPBUFF,&I);
      %end;
%mend DTCNT;
%DTCNT(&STATES);

disconnect from oracle;
QUIT;
ENDRSUBMIT;

Any assistance would be greatly appreciated.

Thanks in advance.

Use macro quoting if you are fine with it. Use `%bquote(&states) ` to mask everything inside. — samkart, May 17 '18 at 15:58
Hi samkart, thank you for your response. I tried that and it doesn't solve the issue. I get the same error. I tried all the macro quoting masks and none of them work. I don't know if they need to be elsewhere in the program but I tried them all over and they don't help. For some reason OR gets passed through as a character for %eval. I'm not sure how to stop it from reading in as a character for the numeric function. — MandyB, May 17 '18 at 16:08
Hi, I did try that. I received another answer that clarified where to use it and some additional information that resolved my issue. Thanks for your time! — MandyB, May 17 '18 at 16:29

score 2 · Accepted Answer · answered May 17 '18 at 16:13

2

The issue here is Oregon. Its abbreviation is OR, which is also a reserved word (oops!). Remember the macro language is just text that is then parsed like normal - so when it finds &ST and translates to OR, it sees that as

%do %while (or ^= )

which causes it to get confused since it doesn't see anything to use with or.

You can use macro quoting here to cause SAS not to treat it like the boolean operator. %SUPERQ is the goto one for me, but a few of them should work.

Here's an example. I added some extra stuff to scan also to handle the parens.

%let states=AK,AZ,CA,HI,ID,NV,OR,WA;
%macro DTCNT() / parmbuff; 
%let i=1;
%put &=syspbuff.;
%let ST=%scan(&SYSPBUFF,&I,%str(%(%),));
%put &=st.;

%do %while (%superq(ST)^=);
%put &=st;
%let i=%eval(&i.+1);
%let ST=%scan(&SYSPBUFF,&I,%str(%(%),));  
%end;
%mend DTCNT;
%DTCNT(&STATES);

answered May 17 '18 at 16:13

Joe

62,789
6
49
67

That did it!!!!!! Thank you so much!! I only needed %str as opposed to %superq but knowing where to put it and adding in the other stuff made ALL the difference. Thanks for your time and response! – MandyB May 17 '18 at 16:27
If you replaced Joe's `%do %while (%superq(ST)^=);` with `%do %while (%str(ST)^=);` the code will not work correctly. I get an infinite loop. %superq (or %bquote) is needed to in order to resolve the macro variable ST and mask the resolved value OR. – Quentin May 18 '18 at 12:09
@Joe, you're right it does. But I don't know why it does. : ) I always find it surprising/confusing when %BQUOTE is used in open code or something like this where %STR() is apparently being used to quote a resolved value. – Quentin May 18 '18 at 14:03
The %str(&st) ^= works perfectly for me in this program. I'm finally able to proceed with my project thanks to Joe! I was using every quoting mask available but was using them in all the wrong spots. I.e. %DTCNT(%STR(&STATE)) and in the syspbuff areas and even tried them in the create table resolve sections. Lol. I DIDN'T try it in the %do %while section. It's just one of those things where you work on something way too long and when you figure it out (or in this case someone else figures it out) it's so simple that all you can do is hit your forehead, shake it off, and continue work. – MandyB May 18 '18 at 14:30
@Quentin I guess the string 'quotes' prevent it from being treated like a boolean operator. Which would suck if you were actually trying to get a boolean operator out of it I guess... – Joe May 18 '18 at 14:32
Agreed Joe, but I think %str() "shouldn't" work to quote the operator since %str() quotes at compile time, not execution time, and the operator isn't seen until the macro variable is resolved. I posted to SAS-L and https://communities.sas.com/t5/Base-SAS-Programming/Odd-use-of-STR-at-execution-time/m-p/463424#M118067 to try to trigger more discussion. – Quentin May 18 '18 at 19:51
@Joe you helped me with this some time ago as you can see. I have a very similar question posted that maybe you can help with? https://stackoverflow.com/q/55386379/9807016 – MandyB Mar 27 '19 at 21:08

score 1 · Answer 2 · edited May 18 '18 at 14:33

1

There is a great paper on how to test for empty macro variables, Chang Chung's Is This Macro Parameter Blank.

For your simple program I find it much easier to just use %length() to test for empty macro variables.

%do %while (%length(&ST));

edited May 18 '18 at 14:33

Joe

62,789
6
49
67

answered May 18 '18 at 03:58

Tom

47,574
2
16
29

Thank you, I'll be sure to take a look at this resource. – MandyB May 18 '18 at 14:30

How to mask "OR" with variable list passed through using SYSPBUFF in macro

2 Answers2