Iteratively list big set of macro var for a proc

[dcortell]

Hi all

I'm trying to perform a proc rank on a large set of macro vars. Code below:

%macro assign;
%do ii=1 %to &max_grams;

%global bigrams&ii 
rank_bigramsⅈ

proc sql noprint;
select

cat('"',trim(_term_),'"n'),

cat('"rank_',trim(_term_),'"n') 


into :bigrams&ii ,
:rank_bigrams&ii

from test
where count=ⅈ
quit;


%end;
%mend;

%assign;

%macro putt_big;
%do ii=1 %to &max_grams;
&&bigramsⅈ  
%end;
%mend;


%macro putt_rank;
%do ii=1 %to &max_grams;
&&rank_bigramsⅈ 
%end;
%mend;

proc transpose data=test out=test_tr
(drop=_name_ _label_) ;
id _term_;
var N;
run;

proc rank data=test_tr groups=10 out=test2; var %putt_big ; ranks %putt_rank; run;

MLOGIC(PUTT_BIG):  %DO loop index variable II is now 101; loop will not iterate again.
MPRINT(PUTT_BIG):   "__a"n "__b"n "__c"n "__d"n "__e"n "__f"n "__g"n "__h"n "__i"n "__j"n "__k"n "__l"n "__m"n "__n"n "__o"n "__p"n 
"__q"n "__r"n "__s"n "__t"n "__u"n "__v"n "__w"n "__x"n "__y"n "__z"n "_a_"n "_aa"n "_ab"n "_ac"n "_ad"n "_ae"n "_af"n "_ag"n 
"_ah"n "_ai"n "_aj"n "_ak"n "_al"n "_am"n "_an"n "_ao"n "_ap"n "_aq"n "_ar"n "_as"n "_at"n "_au"n "_av"n "_aw"n "_ax"n "_ay"n 
"_az"n "_b_"n "_ba"n "_bb"n "_bc"n "_bd"n "_be"n "_bh"n "_bi"n "_bj"n "_bk"n "_bl"n "_bm"n "_bn"n "_bo"n "_bp"n "_br"n "_bs"n 
"_bt"n "_bu"n "_bv"n "_by"n "_bz"n "_c_"n "_ca"n "_cb"n "_cc"n "_cd"n "_ce"n "_cf"n "_cg"n "_ch"n "_ci"n "_ck"n "_cl"n "_cm"n 
"_cn"n "_co"n "_cp"n "_cr"n "_cs"n "_ct"n "_cu"n "_cv"n "_cy"n "_cz"n "_d_"n
MLOGIC(PUTT_BIG):  Ending execution.
ERROR 180-322: Statement is not valid or it is used out of proper order.

However, I'm getting the error above when the macros are called within proc rank:

Pretty sure is something about how the macro are resolved within the macros

Any idea about where to look?

Thanks

[PaigeMiller]

Please show us the ENTIRE log for this PROC RANK. (and in the future, we also need entire log for a given PROC or DATA step, not parts of it)

Have you considered using more meaningful — and easier to type — variable names?

[dcortell]

The macros iterate for 100 times, so I will just add the initial log of the proc rank, as the iterations are all the same for the 100 macro vars:

83    proc rank data=test_tr groups=10 out=test2;
84    var %putt_big ;
MLOGIC(PUTT_BIG):  Beginning execution.
SYMBOLGEN:  Macro variable MAX_GRAMS resolves to      100
MLOGIC(PUTT_BIG):  %DO loop beginning; index variable II; start value is 1; stop value is 100; by value is 1.  
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable II resolves to 1
SYMBOLGEN:  Macro variable BIGRAMS1 resolves to "___"n                                                                              
                                                                                                                                
MPRINT(PUTT_BIG):   "___"n ;
MLOGIC(PUTT_BIG):  %DO loop index variable II is now 2; loop will iterate again.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable II resolves to 2
SYMBOLGEN:  Macro variable BIGRAMS2 resolves to "__a"n                                                                              
                                                                                                                                
NOTE: Line generated by the macro variable "BIGRAMS2".
84     "__a"n
       ------
       180
MLOGIC(PUTT_BIG):  %DO loop index variable II is now 3; loop will iterate again.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable II resolves to 3
SYMBOLGEN:  Macro variable BIGRAMS3 resolves to "__b"n                                                                              
                                                                                                                                
NOTE: Line generated by the macro variable "BIGRAMS3".
84     "__b"n
       ------
       180

The ending part of the log is as the one posted above, where the error happens:

MLOGIC(PUTT_BIG):  %DO loop index variable II is now 100; loop will iterate again.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable II resolves to 100
SYMBOLGEN:  Macro variable BIGRAMS100 resolves to "_d_"n                                                                            
                                                                                                                                  
NOTE: Line generated by the macro variable "BIGRAMS100".
84     "_d_"n
       ------
       180
MLOGIC(PUTT_BIG):  %DO loop index variable II is now 101; loop will not iterate again.
MPRINT(PUTT_BIG):   "__a"n "__b"n "__c"n "__d"n "__e"n "__f"n "__g"n "__h"n "__i"n "__j"n "__k"n "__l"n "__m"n "__n"n "__o"n "__p"n 
"__q"n "__r"n "__s"n "__t"n "__u"n "__v"n "__w"n "__x"n "__y"n "__z"n "_a_"n "_aa"n "_ab"n "_ac"n "_ad"n "_ae"n "_af"n "_ag"n 
"_ah"n "_ai"n "_aj"n "_ak"n "_al"n "_am"n "_an"n "_ao"n "_ap"n "_aq"n "_ar"n "_as"n "_at"n "_au"n "_av"n "_aw"n "_ax"n "_ay"n 
"_az"n "_b_"n "_ba"n "_bb"n "_bc"n "_bd"n "_be"n "_bh"n "_bi"n "_bj"n "_bk"n "_bl"n "_bm"n "_bn"n "_bo"n "_bp"n "_br"n "_bs"n 
"_bt"n "_bu"n "_bv"n "_by"n "_bz"n "_c_"n "_ca"n "_cb"n "_cc"n "_cd"n "_ce"n "_cf"n "_cg"n "_ch"n "_ci"n "_ck"n "_cl"n "_cm"n 
"_cn"n "_co"n "_cp"n "_cr"n "_cs"n "_ct"n "_cu"n "_cv"n "_cy"n "_cz"n "_d_"n
MLOGIC(PUTT_BIG):  Ending execution.
ERROR 180-322: Statement is not valid or it is used out of proper order.
85    ranks %putt_rank;
MLOGIC(PUTT_RANK):  Beginning execution.

[PaigeMiller · Accepted solution]

I think this is nightmare code and nightmare variable names, and I would urge you to consider simplification and using meaningful variable names. Nevertheless, I think your error is here:

%macro putt_rank;
%do ii=1 %to &max_grams;
&&rank_bigramsⅈ 
%end;
%mend;

Specifically, there should not be a semi-colon on line 3. Similarly, the semi-colon in the similar place in %putt_big is incorrect.

If that works, then I give you the homework assignment to see if you can figure out why that is.

[dcortell]

Oh boy, what a miss. Clearly the semi-colon within the macro iterations would be repeated too, and close multiple times the var statement in the proc rank. It's a never ending learning path...Thanks Paige

[ballardw]

First the suggestion to save all of thiseffort:

Sort your TEST data set by _term_ and the use BY _term_ in proc rank for N.

Then if you really need an ugly data set transpose the result, or write a report from the output of proc rank.

I suspect that

Proc sort data=test;
   by _term_;
run;

proc rank data=test groups=10 out=test2;
   by _term_;
   var n;
   ranks rank_n;
run;

and the output from that is likely to much easier to maintain/deal with than multiple macro variables. Also this will handle values of _term_ without having to resort to name literals which won't work if you do not set the system option validvarname=any; That option might be undesirable at other places in code.

Strong suggestion.

Execute your Proc Rank code and manually type in two values of those names. Then show us the log from that manually run code.

Exactly why are you using name literals? I don't see any values that require that.

Note that when you have macros such as this that requires use of a macro variable not provided in the macro parameter list you are setting yourself up for debug problems when the "&max_grams" has not been set or has changed at a time you did not consider it.

%macro putt_big;
%do ii=1 %to &max_grams;
&&bigramsⅈ  
%end;
%mend;

[dcortell]

I need them stored in a macro var as within the program there are steps where the full list of variables need to be called within a Macro function, like %macro(list_of_var); %do something...

[Reeza]

Agreed on bad design, something like this is simpler.

proc contents data=sashelp.cars out=test(rename=(name=_term_) where =(type=1));
run;


data add_rename;
set test;
new_name = catt('rank_', _term_);
run;

proc sql noprint;
select nliteral(_term_) , nliteral(new_name) 
into :var_list separated by " ", :rename_list separated by " " 
from add_rename;
quit;

%put &var_list;
%put &rename_list;


proc rank data=sashelp.cars out=want groups=10;
var &var_list.;
ranks &rename_list.;
run;

[dcortell]

@Reeza It was my initial approach, but as number of variable is extensive (8K - working with ngrams), aggregating them in a unique var, I was getting error: ERROR: The length of the value of the macro variable RANK_BIGRAMS (65540) exceeds the maximum length (65534). The value has been
truncated to 65534 characters.

So needed to resort to separate macro vars

[PaigeMiller]

Other solutions to get around this limitation of macro variable length

• Split the macro variable into N parts, then use all N parts when you call PROC RANK
• CALL EXECUTE

[Reeza]

If working with ngrams, ie individual words, then I'm with @ballardw you should be using BY group processing and not having individual rows and just transpose your final results if needed.