SAS Error

[Sammir]

Hi all,

I have a code that was working fine before migrating to sas 9.4. After migration the code is giving error regarding the macro size.

PROC SQL ;
SELECT
cats("'",_fk,"'")
INTO :string1
SEPERATED BY ","
FROM
referral_lst;
QUIT; 

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

I managed to sort this issue by: 

proc sort data=referral_lst; by _fk ; run;

data _null_;
length mvarlist value_list $32000 mvar $32;
retain mvarlist;
varnum+1;
do until(length(value_list)>30000 or eof);
set referral_lst end=eof;
by _fk ;
if first._fk then value_list=cats(',',value_list,quote(trim(_fk),"'"));
end;
mvar=cats('_fk_list',varnum);
call symputx(mvar,value_list);
mvarlist=cats(',',mvarlist,'&'||mvar);
if eof then call symputx('_fk_list',mvarlist);
run;

now I'm using this macro in a different part of the code:

select distinct

from (
SELECT SUB_KEY, IDENTIFIER, VERSION
FROM _SUBMISSIONS
WHERE src_end_ts = DATE'9999-12-31' and identifier in (&_fk_list.)
) SUB

Now I receive this error:

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

Appreciate your help!

[andreas_lds]

Afaik the macro size was always 65k. Maybe the data has changed, so that more values are written to string1. Also note, that string1 is not used in the subsequent steps.

[Patrick]

To add to @andreas_lds comment:

The migration to SAS 9.4 might also include a change from a single byte to a multibyte session with a default session encoding changing from some single byte code page to UTF-8. 

IF your data contains non-English characters then such characters might now require more than 1 byte for storage and though the "same" data will require more bytes for storage. 

....and should above be the reason for what you observe then you're facing a bigger migration challenge as not all string functions can deal with multi-byte characters - for example substr().

[Kurt_Bremser]

Putting large lists of values into macro variables is just an ERROR waiting to happen.

DATE'9999-12-31

points to a piece of explicit pass through code, so I suggest you upload the lookup data to a temporary table in the DBMS so you can use it in a subselect or join there. This will make the process future-proof.

In a pure SAS environment, I would recommend using a hash object, which can accommodate more values than a macro variable by several orders of magnitude