Help using Base SAS procedures

How to increase macro variable length.

Reply
Super Contributor
Posts: 276

How to increase macro variable length.

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..

Respected Advisor
Posts: 3,156

How to increase macro variable length.

Posted in reply to kuridisanjeev

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

Frequent Contributor
Posts: 139

How to increase macro variable length.

Posted in reply to kuridisanjeev

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.

Super Contributor
Posts: 276

How to increase macro variable length.

Posted in reply to manojinpec

Not sure I understand what you are trying say.

Can you  please explain..??

Super User
Posts: 5,437

How to increase macro variable length.

Posted in reply to kuridisanjeev

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

Data never sleeps
Super Contributor
Posts: 276

How to increase macro variable length.

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

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

to="&email";

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

Super User
Super User
Posts: 7,076

Re: How to increase macro variable length.

Posted in reply to kuridisanjeev

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;

Super Contributor
Posts: 276

Re: How to increase macro variable length.

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.

Super User
Super User
Posts: 7,076

How to increase macro variable length.

Posted in reply to kuridisanjeev

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.

Respected Advisor
Posts: 3,799

Re: How to increase macro variable length.

Posted in reply to kuridisanjeev

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

Super User
Posts: 19,860

How to increase macro variable length.

Posted in reply to kuridisanjeev

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; 

Super User
Posts: 10,044

How to increase macro variable length.

Posted in reply to kuridisanjeev

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

Ask a Question
Discussion stats
  • 11 replies
  • 12232 views
  • 0 likes
  • 8 in conversation