topic Re: Truncation of macro variable with proc sql; select resolve(template) into :final_var in New SAS User

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

dirk_westmalle — Fri, 05 Oct 2018 09:45:37 GMT

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;

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

RW9 — Fri, 05 Oct 2018 10:02:28 GMT

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 &.

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

dirk_westmalle — Fri, 05 Oct 2018 10:06:33 GMT

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.

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

Kurt_Bremser — Fri, 05 Oct 2018 10:13:14 GMT

Maxim 1: read the documentation.

From https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=p1hffi0qkqzu78n1quvvl1szy9k6.htm&locale=en:

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;

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

RW9 — Fri, 05 Oct 2018 11:05:30 GMT

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

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

dirk_westmalle — Fri, 05 Oct 2018 11:07:19 GMT

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

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

dirk_westmalle — Fri, 05 Oct 2018 11:10:00 GMT

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.

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

Kurt_Bremser — Fri, 05 Oct 2018 11:16:54 GMT

@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.

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

dirk_westmalle — Fri, 05 Oct 2018 11:22:24 GMT

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