topic Re: Macro length of the value of the macro variable, exceeds maximum length in SAS Enterprise Guide

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

DME790 — Thu, 28 Sep 2017 01:08:17 GMT

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

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

ShiroAmada — Thu, 28 Sep 2017 01:46:23 GMT

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.

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

stat_sas — Thu, 28 Sep 2017 01:47:03 GMT

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;

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

Tom — Thu, 28 Sep 2017 02:19:22 GMT

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.

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

ballardw — Thu, 28 Sep 2017 14:57:29 GMT

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.

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

PaigeMiller — Thu, 28 Sep 2017 15:07:05 GMT

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

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

ShiroAmada — Fri, 29 Sep 2017 01:35:48 GMT

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

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

sam101 — Thu, 12 Jul 2018 07:04:45 GMT

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;