topic Re: Error while creating macro variable using SQL Into in SAS Programming

Error while creating macro variable using SQL Into

ambadi007 — Wed, 27 Jul 2016 13:44:18 GMT

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

Re: Error while creating macro variable using SQL Into

Astounding — Wed, 27 Jul 2016 14:07:33 GMT

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.

Re: Error while creating macro variable using SQL Into

ballardw — Wed, 27 Jul 2016 16:32:22 GMT

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.

Re: Error while creating macro variable using SQL Into

sam101 — Wed, 18 Jul 2018 07:24:49 GMT

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;