Problem with maximum length of expression in user transformation(SAS Data Integration Studio)

[Realmadrid1992]

Hi, SAS! I work in data integration studio and have problem with the length of expression:

ERROR: The text expression length (65535) exceeds maximum length (65534). The text expression has been truncated to 65534 characters.

It happens in my user written transform, when sas resolves macro variable:

%let _OUTPUT1_col9_exp = /*{long expression, which contains very long data step code, for example: "if number=1 then do....end; if....." and has length >65535}*/;

I understand, that this very long text expression assigns in macro variable, and it has length limit 65534 

but may be someone faced such problem and successfully solved it. My user written transformation doesn't allow me to split this macro variable into several macro variables, because it use in data step code:

..........
data &&_OUTPUT&mvI.(keep=&&_OUTPUT&mvI._keep &&_OUTPUT&mvI._options) %if "&&_OUTPUT&mvI._memtype" eq "VIEW" %then / view=&&_OUTPUT&mvI.;
    attrib
    /*{attrib code}*/
    set 
     %do mvI=0 %to %eval(&_INPUT_count - 1);
        &&_INPUT&mvI. (in=in_&mvI. &&_INPUT&mvI._options)
    %end;    
    %do mvI=0 %to %eval(&&_OUTPUT&mvT._col_count - 1);
        %if "&&_OUTPUT_col&mvI._exp" ne "" %then %do;
            &&_OUTPUT&mvT._col&mvI._exp;   {<- this is the place, where this macro variable resolves}
        %end;
    %end;
run;
.......

  I tried to split my code into several expressions:

%let _OUTPUT1_col9_exp = /*{part 1 of long expression, which contains very long data step code, for example: "if number=1 then do....end; if....." and has length >65535}*/;
%let _OUTPUT1_col10_exp = /*{part 2 of long expression, which contains very long data step code, for example: "if number=1 then do....end; if....." and has length >65535}*/;
%let _OUTPUT1_col11_exp = /*{part 3 of long expression, which contains very long data step code, for example: "if number=1 then do....end; if....." and has length >65535}*/;
......

but i have limited quantity of _OUTPUT1_col{n}_exp macrovariables, and it is not enough to fit this long expression.

Sorry for my English, I'm from Belarus😶 and i don't hope that someone can help me with this problem.

[ChrisNZ · Accepted solution]

Welcome to the community and congrats on a complete and well-formatted post!

There's no way to make the macro variables longer.

You can either make several variables and call

           &&_OUTPUT&mvT._col&mvI._exp1 
           &&_OUTPUT&mvT._col&mvI._exp2;   

or save your code in an external file and include it. Like this:

filename TMP temp;
data _null_;
  file TMP ;
  put 'put "Hello!"; ';
run;

data _null_;
  %include TMP ;
run;

[Realmadrid1992]

I tried to use the "external file" way.
I didn't say, that my long text expression is the result of:
1. data step, which generate macro variables through the call symput statement
2. this macro variables generate my expression through the macro loop.

in short situation is:

/*FIRST POINT*/
data _null_;
    set product_table;
    .........
    /*some data step code*/
    .........
    call symput('mvCount',strip(product_count));
    call symput('mvProduct'||left(product_count),strip(product));
    call symput('mvProductStatement'||left(product_count),strip(prod_statement));
    output;
run;

/*SECOND POINT*/
%macro mExpressionMacroLoop;
    %do mvNum=1 %to &mvCount;
        if product=&&mvProduct&mvNum then do%str(;)
            &&mvProductStatement&mvNum;
	end%str(;)	
%mend mExpressionMacroLoop;

And now i need to write the result of execution of %mExpressionMacroLoop to TMP file. I know the call execute function, but i don't now how to connect it with PUT statement, which write to file:

filename TMP tmp;
data _null_;
    file  TMP;
    put '/*here i need to write the result of %mExpressionMacroLoop execution*/';
run;

Could you help me with the PUT statement in this way, because i  worked with the external files by SAS very rarely and have no idea how to write it correctly? 

[ChrisNZ]

Unsure of what your data is like, but it seems that this could help:

/*FIRST POINT*/
data _null_;
    set product_table;
    .........
    /*some data step code*/
    .........
    put 'if PRODUCT =' PRODUCT $quote. ' then do;' ;
    put PROD_STATEMENT ;
    put 'end;' ;
run;

[ErikLund_Jensen]

Hi @Realmadrid1992 

It is certainly possible to solve this problem. The best solution will be to recode the expressions to a shorter form with the same functionality, and I have an idea:

User-written transformations do not generate any code that make use of the macro variables, they are only defined so the content is available to the user-written code, and anyhing that DI Studio accepts as a  syntactically correct expression will work. This means that the expression could be a macro call with the input column name as argument.  

So if you write the code in a macro and store the macro in a macro catalog allocated to the session, you could write the expression as

%recodecolumn(input_variable) or whatever name you prefer for the macro. 

The macro argument is there only to allow column mapping in DI Studio and thus preserving column-lineage. The macro should contain the expression and nothing else. It should resolve to code lines in the data step, so your data step should work as written without modifications, the only difference is that the code is supplied from the macro and not passed in the DI Studio generated macro variables.

Let my know if you like the idea, and I will make a working example.

[Realmadrid1992]

Thanks. I solved my problem by using user %include file in data step with necessary text expression