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;
@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.
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 &.
Maxim 1: read the documentation.
(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 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.
Interesting, I have not used proc json so must get round to looking at it at some point.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.