Truncation of macro variable with proc sql; select resolve(template) into :final_var

[dirk_westmalle]

Hi,

Can someone explain why the first proc sql in the below example will truncate the resolved macro variable ?

I was under the impression that macro variables can contain 65K and that resolving should only truncate if the final content exceeds the 65K border. As such it seemed reasonable to leave out a length statement in the proc sql.

The second proc sql does not truncate the resolved variable, because I specified a length.

Specifying a final length like in the second proc sql is not an option as we have more complex uses (multiple variables which are resolved multiple times) and trying to find the final length, would mean that we would have to actually recreate the resolve logic ...

Thanks in advance to anyone that can help me to understand this issue.

Dirk

%let extraText=qwertzuiopasdfghjklöyxcvbnm;

data test;

format template $260.;

template='123456789012345678901234567890 &extraText 123456789012345678901234567890 123456789012345678901234567890 123456789012345678901234567890 123456789012345678901234567890 123456789012345678901234567890 123456789012345678901234567890 ';

run;

proc sql;

/*This one will truncate the resolved value */

select resolve(template)

into :final_var separated by ''

from test;

run;

proc sql;

/*This one will contain the complete resolved value*/

select resolve(template) length=512

into :final_var separated by ''

from test;

run;

[RW9]

Have a peek in the test dataset, you will see the string is given a length of 260.  Now when the macro reference is resolved, and the text "qwertzuiopasdfghjklöyxcvbnm" added into that string, this pushes the length of the string past the 260 characters, so truncation happens.  By telling SAS the length, you have extended the number of characters the variable has, so no truncation is applied.

I really would however wonder, why you are making life so complicated for yourself.  The use of resolve, and macro's in text variables strongly indicates a bad process, much like more then one &.

[dirk_westmalle]

Does that mean that SAS will put the resolved value back in the table ? Sounds strange

We use this technique to create complex json structures (with proc json ) dynamically.

[Kurt_Bremser]

Maxim 1: read the documentation.

From https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarg...:

Details

If the RESOLVE function returns a value to a variable that has not yet been assigned a length, by default the variable is assigned a length of 200 bytes.

(Emphasis by me)

The length of your macro variable is in fact 200, which can be verified by

data _null_;
length = length("&final_var");
put length=;
run;

[dirk_westmalle]

Shame on me 🙂
Should have read the manual. Is there a way to have this more dynamic

[Kurt_Bremser · Accepted solution]

---

@dirk_westmalle wrote:
Shame on me 🙂
Should have read the manual. Is there a way to have this more dynamic

---

In the proc sql, do this:

proc sql;
/*This one will truncate the resolved value */
select resolve(template) length=32767
into :final_var separated by ''
from test;
run;

(use the maximum character variable length)

The macro variable itself is automatically trimmed anyway, courtesy of the "separated by" clause (you could also use "trimmed"):

data test;
test = "&final_var";
run;

Variable test will have a length of 244. Without separated or trimmed, it would be 32767 characters.

[dirk_westmalle]

I'll inform my colleagues to use this consistently from now on !
Thanks to all for the super fast replies !!!

[RW9]

Interesting, I have not used proc json so must get round to looking at it at some point.

[dirk_westmalle]

We use assemble a proc json with a complex model in a temporary file. When the complete proc json is created, then we include that temporary file to generate the final json object, output it to the _webout and let a HTML page digest the data with JQuery code. Using proc json without the intermediate file does not allow complex data structures.