BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DME790
Pyrite | Level 9

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
ShiroAmada
Lapis Lazuli | Level 10

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
Ammonite | Level 13

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
Super User Tom
Super User

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
Super User

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
Diamond | Level 26

@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
ShiroAmada
Lapis Lazuli | Level 10

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
Calcite | Level 5

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;
 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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