DATA Step, Macro, Functions and more

Error while creating macro variable using SQL Into

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Error while creating macro variable using SQL Into

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

 

 

 


Accepted Solutions
Solution
‎07-27-2016 10:48 AM
Super User
Posts: 5,085

Re: Error while creating macro variable using SQL Into

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.

View solution in original post


All Replies
Solution
‎07-27-2016 10:48 AM
Super User
Posts: 5,085

Re: Error while creating macro variable using SQL Into

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.

Super User
Posts: 10,516

Re: Error while creating macro variable using SQL Into

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 219 views
  • 2 likes
  • 3 in conversation