BookmarkSubscribeRSS Feed
kuridisanjeev
Quartz | Level 8

proc sql ;
select distinct(email_id) into: mail separated by ','  from temp;

quit;

Nothing wrong with above code but still i am getting error due to macro variable length..

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

How resolve this..???

Thanks in advance..

11 REPLIES 11
Haikuo
Onyx | Level 15

IMHO, There is nothing you could do on the length alone. You probably have to somehow subset your data and use more than one macro variable.

Haikuo

manojinpec
Obsidian | Level 7

may be you can create a virtual key and then store the virtual key in macro variable and lookup your actual variable using the virtual key.

kuridisanjeev
Quartz | Level 8

Not sure I understand what you are trying say.

Can you  please explain..??

LinusH
Tourmaline | Level 20

If you explain why you want a bunch of emails in a macro variable, maybe someone can suggest a better approach?

Data never sleeps
kuridisanjeev
Quartz | Level 8

I am using that macro variable in filename statement...

filename email from="**************@####.com"

to="&email";

..............so on.....

Tom
Super User Tom
Super User

Just write the code to a file.

filename email temp;

data _null_;

  file email;

  set temp end=eof ;

  if _n_=1 then put 'filename email from="xxxxxxxx" to="'  mail ;

else put ',' mail @;

if eof then put '";' ;

run;

%inc email;

kuridisanjeev
Quartz | Level 8

Sorry TOM, i did't get you.

insted of writing all the mail id's in "to=" option ,i am storing all the mail id's in a silgle macro variable and i am puting that variable in to= option.

Tom
Super User Tom
Super User

But you said that you have too many addresses to put into a macro variable. 

So instead of storing them in a macro variable store them in a file.

Test if the FILENAME statement has the same limit.

You are probably better off reading the documentation that DATA _NULL_ points you to so that you can figure out how to generate the TO addresses from your data.

data_null__
Jade | Level 19

Look up this topic in your documentation "Specifying E-mail Directives in the PUT Statement".

Reeza
Super User

See this little tidbit from SAS posted recently. I haven't had a chance to test it out, so let me know if it works.

http://support.sas.com/kb/46/109.html

data t;                                                                                                                                
input key;                                                                                                                             
cards;                                                                                                                                 
1                                                                                                                                      
-1                                                                                                                                     
3                                                                                                                                      
;                                                                                                                                      
                                                                                                                                       
%macro test(dsn);                                                                                                                      
%let dsid=%sysfunc(open(&dsn(where=(key > 0))));                                                                                    
%let cnt=%sysfunc(attrn(&dsid,nlobsf));                                                                                               
%let num=%sysfunc(varnum(&dsid,key));                                                                                                 
  %do i = 1 %to &cnt;                                                                                                                  
   %let rc=%sysfunc(fetchobs(&dsid,&i));                                                                                               
   %let val=%sysfunc(getvarn(&dsid,&num));                                                                                             
%if &i ne &cnt %then %do;                                                                                                              
&val,                                                                                                                                  
%end;                                                                                                                                  
%else %do;                                                                                                                             
&val                                                                                                                                   
%end;                                                                                                                                  
%end;                                                                                                                                  
                                                                                                                                       
%let rc=%sysfunc(close(&dsid));                                                                                                        
%put dd;                                                                                                                               
%mend test;                                                                                                                            
                                                                                                                                       
data new;                                                                                                                              
new="%test(t)";                                                                                                                       
run;                                                                                                                                   
                                                                                                                                       
proc print;                                                                                                                            
run; 

Ksharp
Super User

For your situation, I usually try to use two ways.

1. Use a single macro variable to store a single e-mail.

proc sql ;
select distinct(email_id);

select distinct(email_id) into: mail1-mail&sqlobs. ;

quit;

%macro mail;

filename email from="**************@####.com"

to="

%do i=1 %to &sqlobs. ;

&&mail&i   

%if &i ne &sqlobs %then %do;  ,  %end;

%end;

l";

................

%mend mail;

%mail

2. Use Call execute ,actually it is the same with Tom's code,but more convenience.

data mail;

input mail $40. ;

cards;

Tom@tom.com

Art@art.com

Patrick@patrick.com

;

run;

data _null_;

set mail end=last ;

if _n_ eq 1 then call execute(' filename email from="**************@####.com" to="   '   );

call execute ( mail );

if not last then call execute(' , ');

if last then call execute( '............................................' );

run;

Ksharp

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 20706 views
  • 1 like
  • 8 in conversation