How to mask “OR” in sub-macro 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 (inner query) and regions (outer query). One of the states being used is Oregon which abbreviates to "OR" and that one state is causing me error.

I previously asked an almost identical question and got a response that solved the problem. However, now I am adding the working macro within another macro and it no longer works anymore. What am I missing in the outer macro that's causing the inner macro not to work anymore??? I should clarify...it works until it gets to Oregon. So the macro is functional. Again, the inner query works properly and recognized OR as a state value until I put it inside the outer query, then it stops recognizing OR as a state value.

Here is my current code:

%macro ALLRG() / parmbuff; 
%let r=1;
%let RG=%scan(&SYSPBUFF,&R);  

%do %while (%str(&RG)^=);

%syslput NUM=&RG;   
%let NUM=&RG;               
%syslput STATES=&&STATESR&NUM;   
%let STATES=&&STATESR&NUM;      

RSUBMIT;
PROC SQL inobs=1;
connect to oracle
(path=OIGDW ******);
%macro VSNLT() / parmbuff; 
%let i=1;
%let ST=%scan(&SYSPBUFF,&I);  

%do %while (%str(&ST)^=);
CREATE TABLE PHL_&ST._PROV_05_VRSN AS
select TMS_RUN_TIMESTAMP
from connection to oracle
   (SELECT TMS_RUN_TIMESTAMP    
    from r&NUM._own.&ST._PROV_05);

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

 disconnect from oracle;
 QUIT;
 ENDRSUBMIT;

     %let r=%eval(&R+1);  
     %let RG=%scan(&SYSPBUFF,&R);
     %end;
 %mend ALLRG;
 %ALLRG(1,2,3,4,5,6,7,9);

The state tables are within regional schemas so I have to access multiple shemas and tables to get all the states. So I want the outer macro to set the regional and state parameters for the inner macro.

Sometimes macro quoting can be a bit tricky. Can you post which line the error is coming from? Without any other information, I'd recommend trying the following: 1. Change `%str()` to `%bquote()`. 2. Remove `%str()` and change `%scan()` to `%qscan()` in the first and second `%let ST=%scan()` statements. — Stu Sztukowski, Mar 28 '19 at 00:35
Thank you @StuSztukowski for your time and response. I had just found the solution a few moments before this but given the limited information you had to go off of as far as where the error occurred from, you certainly were on the right track and I believe your changes would've helped! Again, thanks! — MandyB, Mar 28 '19 at 00:39
I found the solution in another post: [Find Here](https://stackoverflow.com/a/35799880/9807016) It comes down to the difference in when %str and %quote compiles. In this case my inner query needs to use %quote instead of %str in the following line: `%do %while (%str(&ST)^=);` Once I made that change, it started working and I get all 50 states included the dreaded ORegon! Thanks @Joe for your answer to the other post that also solved this problem! — MandyB, Mar 28 '19 at 00:43

score 1 · Answer 1 · answered Mar 28 '19 at 00:37

1

The %superq() macro function is probably what you want to use to insure macro quoting. You might also want to use %qscan() instead of %scan().

%let RG=%qscan(%superq(SYSPBUFF),&R);

I am also not clear why you are pushing a macro definition into the remote session. That extra level of complexity might not be needed. Also the pushing of a macro definition via rsubmit, especially within a macro, can cause SAS to mess up the parsing of the code. If you really need a macro defined over there define it via autocall or %include. Upload the text using proc upload if you have to.

answered Mar 28 '19 at 00:37

Tom

47,574
2
16
29

Thanks @Tom. You've given me some things to think about. We usually use remote session because the amount of data we are working with bogs down our local machines too much. If we utilize RSUBMIT then we can let the processing power happen in a much more capable computing environment that doesn't slow our local machines down. I have run into issues in the past with certain macros not working the same in the remote environment but this one seems to be okay. Thanks for your time and response. – MandyB Mar 28 '19 at 00:50

How to mask “OR” in sub-macro with variable list passed through using SYSPBUFF in macro

1 Answers1

Linked