Lenght of macro variable for view creating problem

[msf2021]

Hey everyone,

I'm trying to do a view as an agreggation of tables that already exist but when i'm getting the following error : 

ERROR: The length of the value of the macro variable TABELAS (65540) exceeds the maximum length (65534). The value has been 
       truncated to 65534 characters.

Here's my code: 

%macro create_mega_biew;
%local tabelas;

proc sql noprint;
select cat('select * from XXXX.', strip(memname)) into :tabelas separated by ' OUTER UNION CORR '
from sashelp.vtable
where libname='XXXX' AND UPCASE(MEMNAME) LIKE 'RV_LL%';
quit;

%put &=tabelas;

proc sql;
%if %sysfunc(exist(WORK.MINHA_VIEW, VIEW)) %then drop view WORK.MINHA_VIEW;
%else %if %sysfunc(exist(WORK.MINHA_VIEW)) %then drop table WORK.MINHA_VIEW;
;
create view WORK.MINHA_VIEW as &tabelas;
quit;
%mend create_mega_biew;

%create_mega_biew

* Inspect newly created view;
proc sql;
describe view WORK.MINHA_VIEW;
quit;

Can anyone help me ?

Thanks!

[PaigeMiller]

As always, context is everything, and you have given us no context. Please explain, in words, what you are doing and what you expect to do after you have created this data set WORK.MINHA_VIEW. Knowing the big picture will help us figure out what a good approach is. Thanks.

[Tom]

Your problem does not look like it needs any code generation at all.  If you want is to make a VIEW that combines all datasets in XXXX that start with RV_LL then just run this code:

data MINHA_VIEW / view=MINHA_VIEW ;
  set XXXX.RV_LL: ;
run;

The only conditional code you MIGHT need is if there happens to already be a DATASET with that same name, in which case you could not create a VIEW with that name.  So you might need to add this code, which you can now run in OPEN CODE without having to create a macro.

%if %sysfunc(exist(WORK.MINHA_VIEW, DATA)) %then %do;
proc delete data=MINHA_VIEW;
run;
%end;

And if you did need to generate code why put the code into a macro variable?  Just write the code to a file and %INCLUDE the file. So something like this:

proc sql ;
create tabla RV_LL_tables as
  select catx(',',libname,nliteral(memname)) as dsname
  from dictionary.tables
  where libname='XXXX' AND UPCASE(MEMNAME) LIKE 'RV_LL%'
;
quit;

filename code temp;
data _null_;
  set RV_LL_tables end=eof;
  file code;
  if _n_=1 then do;
    put 'proc sql;';
    if exist('WORK.MINHA_VIEW', 'DATA') then put 'drop table WORK.MINHA_VIEW;';
    put 'create view WORK.MINHA_VIEW as';
  end;
  else put 'OUTER UNION CORR';
  put 'select * from ' dsname ;
  if eof then put ';'/'quit;' ;
run;
%include code / source2;

[andreas_lds]

Trying to store so much information in macro variable indicates bad design in 100% of the cases I saw in the last 15+ years.