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

[DME790]

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

[ShiroAmada]

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.

[stat_sas]

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;

[Tom · Accepted solution]

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. 

[ballardw]

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.

[PaigeMiller]

---

@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;

[ShiroAmada]

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

[sam101]

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.2;)  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;