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: 108
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: 7,847

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: 113

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: 7,847

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,023

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: 2,649

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: 113

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

 

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 843 views
  • 2 likes
  • 6 in conversation