I can't increase macro variable limit

[Moacyr007]

Hi,

i am trying to get some data, based on a macro variable, but get the error

ERROR: The length of the value of the macro variable LISTA_DESTINATARIOS (65540) exceeds the maximum length (65534). The value has

Been truncated to 65534 characters.

I tried do make de macro limit bigger with 

Options mexecsize=800000;

Options MVARSIZE =800000;

Options MSYMTABMAX=800000;

But doesn’t work, i get de error:

ERROR 18-12: Option value for SAS option MVARSIZE must be between 0 and 65534

i tried do get de date of using a table instead of a macro variable but the query keep running for ever, a think this happen because of something about get data from oracle tables and sas tables. With macro almost work, i can get the date until reach macro limit.

not English native speaker pls be patient

I am using SAS Enterprise Guide 7.1

thanks a lot

[Reeza]

You cannot increase the size of macro variables beyond a certain limit. If you check the documentation you'll see there's a maximum value of 65,534, which you're trying to go beyond. If you explain your use case we can likely offer alternative options.

[Kurt_Bremser]

The maximum size of a macro variable is 64K, period. It's a constructive limit of SAS software, just like the 32K limit for character variables in datasets and data steps.

What are you trying to store in the macro variable, and how do you do it?

[Moacyr007]

i am storing 19k rows of numbers with 6 to 14 digits, with

SELECT DISTINCT NUMR_PESSOA INTO :LISTA_EMISSORES SEPARATED BY ',' FROM WORK.QUERY_LISTA_EMISSORES;

I use these numbers later on a where statement, i tried use a select in the where but just keep running and doesn't end

[novinosrin]

Post a clear sample of your requirement. The requirement's solution is to use look up tables rather than large macro vars. What kind of look up tables, be it format/hash/join/array, leave that for the community to offer. 

[Kurt_Bremser]

Do a join on those numbers, or use a hash object in a data step. This is absolutely NOT what the macro language is made for.

[PaigeMiller · Accepted solution]

---

@Moacyr007 wrote:
i am storing 19k rows of numbers with 6 to 14 digits, with

SELECT DISTINCT NUMR_PESSOA INTO :LISTA_EMISSORES SEPARATED BY ',' FROM WORK.QUERY_LISTA_EMISSORES;

I use these numbers later on a where statement, i tried use a select in the where but just keep running and doesn't end

---

CALL EXECUTE may help here (depending on what you are doing), and there is no maximum character limit when you use CALL EXECUTE.

[PaigeMiller]

Now that I have another minute, you want a WHERE clause that has the 19000 numbers, something like

where variable in (1,2,3,...,19000)

except that the numbers inside the parenthesis are not the sequential numbers 1 through 19000, they are the 6 to 14 digit values of variable NUMR_PESSOA.

Is that what you are going for? If so, then I stick with my comment above, this ought to be relatively easy using CALL EXECUTE. If I had more free time today, I could write the code for you. I'm sure someone else will jump in.

[Reeza]

You can also generate a macro that will just put those values into the WHERE statement. SAS used to have a usage example of how to do that but it disappeared for some reason.

[Tom]

If you really, really need to stuff a lot of information into macro variables then look at this answer.

https://communities.sas.com/t5/New-SAS-User/Using-INTO-to-create-a-macro-variable-for-pass-through-t...

The idea is to create multiple macro variables that each have some of the the list and then create another master macro variable that has references to the others.

So instead of 

%let list=1 2 3 4 5 6 7 8 9;

You do something like:

%let list1=1 2 3  ;
%let list2=4 5 6 ;
%let list3=7 8 9;
data _null_;
 call symputx('list','&list1 &list2 &list3');
run;

Then when you reference &LIST you get all three of the other macro variables expanded.

Another method is to use a data step to generate the code from the data. Either with CALL EXECUTE() or by writing to a text files and using %INCLUDE to run the generated code.

[ballardw]

And an approach using Proc Format:

Proc sql;
   create table listcntlin as
   SELECT DISTINCT NUMR_PESSOA as start,'Match' as Label 
       ,'MyList' as Fmtname, 'N' as type 
FROM WORK.QUERY_LISTA_EMISSORES; 

proc format library=work cntlin=listcntlin;
run;

data example;
   set somedataset;
   where put(variable,Mylist.)='Match';
run;
proc sql;
   create table example2 as
   select *
   from somedataset
   where put(variable,Mylist.)='Match'
   ;
quit;

If you need the same list of values then make that a format in a permanent library in your FMTSEARCH path. If not, then this goes away at the end of your session like macro variables. And does not hit the macro space at all.