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
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.
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.
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.
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;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.