Error while creating macro variable using SQL Into

[ambadi007]

HI Team ,

I am trying to create a macro variable to fetch only PI Claims like below and

proc sql;

create table PI_LOOKUP as

select *

from connection to odbc

( select

claim_id AS CLAIM_NBR

FROM

CLAIM_POL_REF

);

QUIT;

PROC SQL noprint;

SELECT "'" || STRIP(CLAIM_NBR) || "'" INTO : CLM_LIST_PI

SEPARATED BY ", "

FROM PI_LOOKUP ;

QUIT;

Iam getting the below error message

ERROR: The length of the value of the macro variable CLM_LIST_PI (65540) exceeds the maximum length (65534). The value has

been truncated to 65534 characters.

Is there any restrictions up to a maximum leangth ? Please advice or suggest me how to create a macro variale in another method or way.

Thanks

[Astounding · Accepted solution]

Yes, you have hit the limit.  Macro variables do have a maximum length.

It looks like you may be intending to use the macro variable in a later WHERE clause such as:

where claim_nbr in (&pi_lookup)

If that's the case, you might get around the limit by using a subquery:

where claim_nbr in (select claim_nbr from pi_lookup)

Also note, if you have the possibility of duplicate claim numbers that you might add DISTINCT here and there.

[ballardw]

Note that you can adjust the size of the macro variable limit. The system option MVARSIZE can be used in an options statement to increase (or decrease) the limit;

options mvarsize=1M; for instance would set the limit to 1 megabyte. The numeric part of the option is limited in range 0 to 65534 but you may suffix that value with K (kilobytes), M (Megabytes0 and G(Gigabytes).

However I suspect that you might reconsider the approach that requires that long of a list.

[sam101]

I am having same problem . When I tried to use MVARSIZE I got error (ERROR 18-12: Option value for SAS option MVARSIZE must be between 0 and 65534.) . I also tried to use options MEXECSIZE but did not worrked. I am new to SAS so please if someone can help me with this problem.

libname lib '/folder/my';
OPTION MEXECSIZE=max;
option mvarsize=1M;
%macro fun;
*proc options mexecsize=10;
*run; 
/*proc OPTIONS MEXECSIZE=max;
run;  */



PROC SQL NOPRINT ;

CREATE TABLE lib.VAR_NAMES AS
  SELECT NAME FROM DICTIONARY.COLUMNS
        WHERE libname ='LIB' AND memname ='MGR';
        

SELECT COMPRESS(NAME) INTO :UNIT_VAR_LIST SEPARATED BY " " FROM lib.VAR_NAMES WHERE UPCASE(name) LIKE '%unit%' OR
UPCASE(name) LIKE UPCASE('%UNIT%');


SELECT COMPRESS(NAME) INTO :DLLR_VAR_LIST SEPARATED BY " " FROM lib.VAR_NAMES WHERE UPCASE(name) LIKE '%dllr%' OR
UPCASE(name) LIKE '%DLLR%';
QUIT;




%mend fun;

%fun;