Desktop productivity for business analysts and programmers

Macro length of the value of the macro variable, exceeds maximum length

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 129
Accepted Solution

Macro length of the value of the macro variable, exceeds maximum length

Hi All,

 

I have seen posts on this however I can't seem to work my head around it.

 

I have a 'list' of 11,152 logons (this number can change) which I use a the proc SQL to create.

 

proc sql; 
   
      select quote (trim(Employee_ID)) into :Name_List
	separated by " "
	from work.janunique;

   quit; 

and it returns the error;

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

 

I use the list in the following where statement

 

Data work.Jan_Unallocated;
	Set Data.Employee_Telephony;
	Keep EVENT_DT	DIVISION_NM	BRANCH_NM	PROGRAMME_NM_CT	CENTRE_TYPE	LOGON_ID	EMPLOYEE_NAME	TOTAL_CALLS	WORK_TIME;
	Where Event_DT between  '01Jan2017.'d and '31Jan2017.'d
		and Logon_ID in (&name_list);

Run;

Is there a way I can get around this error or change the Data step to allow the list to be used.

 

Thanks in advance.

 

Cheers

 

Dean

 

 

 

 


Accepted Solutions
Solution
‎10-02-2017 09:00 PM
Super User
Super User
Posts: 8,272

Re: Macro length of the value of the macro variable, exceeds maximum length

[ Edited ]

The easiest way it to not move the data into text at all.  Just use a sub-query in SQL to compare the list of values.

proc sql ;
  create table Jan_Unallocated as
    select *
    from  Data.Employee_Telephony
(keep= EVENT_DT DIVISION_NM BRANCH_NM PROGRAMME_NM_CT
 CENTRE_TYPE LOGON_ID EMPLOYEE_NAME TOTAL_CALLS WORK_TIME)
    where Event_DT between '01Jan2017'd and '31Jan2017'd
      and Logon_ID in (select Employee_ID from work.janunique)
  ;
quit;

If the actually the two tables are really in different database systems so that using sub-query is not possible then you could use a data step to write the WHERE statement to a text file and use %INCLUDE to add it to your code. 

View solution in original post


All Replies
Frequent Contributor
Posts: 118

Re: Macro length of the value of the macro variable, exceeds maximum length

Try this one...

 

proc sql;
  create table jan.unallocated as
select

 EVENT_DT,	DIVISION_NM,	BRANCH_NM,	PROGRAMME_NM_CT,	CENTRE_TYPE,	LOGON_ID,	EMPLOYEE_NAME,	TOTAL_CALLS,	WORK_TIME

from Data.Employee_Telephony
where

Event_DT between  '01Jan2017.'d and '31Jan2017.'d
		and Logon_ID in (    select strip(distinct Employee_ID) 
	from work.janunique) ;
quit;

 

Hope this helps.

Trusted Advisor
Posts: 1,270

Re: Macro length of the value of the macro variable, exceeds maximum length

Hi,

 

One way to do this using sql:

 

proc sql;
create table Jan_Unallocated as
select EVENT_DT, DIVISION_NM, BRANCH_NM, PROGRAMME_NM_CT, CENTRE_TYPE, LOGON_ID, EMPLOYEE_NAME,       TOTAL_CALLS, WORK_TIME
from Data.Employee_Telephony
Where Event_DT between '01Jan2017.'d and '31Jan2017.'d
and trim(Logon_ID) in (select trim(Employee_ID) from work.janunique);
quit;

Solution
‎10-02-2017 09:00 PM
Super User
Super User
Posts: 8,272

Re: Macro length of the value of the macro variable, exceeds maximum length

[ Edited ]

The easiest way it to not move the data into text at all.  Just use a sub-query in SQL to compare the list of values.

proc sql ;
  create table Jan_Unallocated as
    select *
    from  Data.Employee_Telephony
(keep= EVENT_DT DIVISION_NM BRANCH_NM PROGRAMME_NM_CT
 CENTRE_TYPE LOGON_ID EMPLOYEE_NAME TOTAL_CALLS WORK_TIME)
    where Event_DT between '01Jan2017'd and '31Jan2017'd
      and Logon_ID in (select Employee_ID from work.janunique)
  ;
quit;

If the actually the two tables are really in different database systems so that using sub-query is not possible then you could use a data step to write the WHERE statement to a text file and use %INCLUDE to add it to your code. 

Super User
Posts: 13,913

Re: Macro length of the value of the macro variable, exceeds maximum length

There are System options available such as MVARSIZE to change the maximum allowable length of a single macro variable. However when a single varaible starts exceeding the 64K size I usually think that is a strong indicator that an alternate approach should be considered.

Respected Advisor
Posts: 3,261

Re: Macro length of the value of the macro variable, exceeds maximum length


DME790 wrote:

Hi All,

 

I have seen posts on this however I can't seem to work my head around it.

 

I have a 'list' of 11,152 logons (this number can change) which I use a the proc SQL to create.

 

proc sql; 
   
      select quote (trim(Employee_ID)) into :Name_List
	separated by " "
	from work.janunique;

   quit; 

and it returns the error;

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

 

I use the list in the following where statement

 

Data work.Jan_Unallocated;
	Set Data.Employee_Telephony;
	Keep EVENT_DT	DIVISION_NM	BRANCH_NM	PROGRAMME_NM_CT	CENTRE_TYPE	LOGON_ID	EMPLOYEE_NAME	TOTAL_CALLS	WORK_TIME;
	Where Event_DT between  '01Jan2017.'d and '31Jan2017.'d
		and Logon_ID in (&name_list);

Run;

Is there a way I can get around this error or change the Data step to allow the list to be used.

 

Thanks in advance.

 

Cheers

 

Dean


In your PROC SQL code, you need to make one minor change

 

select DISTINCT quote(trim(Employee_ID)) into :Name_List
	separated by " "
	from work.janunique;
--
Paige Miller
Frequent Contributor
Posts: 118

Re: Macro length of the value of the macro variable, exceeds maximum length

Your where clause 

	Where Event_DT between  '01Jan2017.'d and '31Jan2017.'

should be without a dot in the sas date values like this 

 

	Where Event_DT between  '01Jan2017'd and '31Jan2017'd

 

New Contributor
Posts: 3

Re: Macro length of the value of the macro variable, exceeds maximum length

[ Edited ]
Posted in reply to ShiroAmada

Hi All ,

 

I am also getting " 

ERROR: The length of the value of the macro variable UNIT_VAR_LIST (65540) exceeds the maximum length (65534). The value has been
truncated to 65534 characters."  but when I searched I read about option "mexecsize" which is available to solve this issue but I do not know how to use "mexecsize" option . Can anyone help me on this this issue ?
I Have written this code to format all SAS varibles (FORMAT &UNIT_VAR_LIST 9. ;
FORMAT &DLLR_VAR_LIST 9.2Smiley Wink  at once.
libname lib '/my/sas/var_size_issue';


*proc options mexecsize=MAX;
*run; 
*MEXECSIZE = MAX;

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;
 
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 1133 views
  • 2 likes
  • 7 in conversation