topic Re: How to increase macro variable length. in SAS Procedures

How to increase macro variable length.

kuridisanjeev — Thu, 29 Mar 2012 10:53:40 GMT

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

How to increase macro variable length.

Haikuo — Thu, 29 Mar 2012 11:24:02 GMT

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

How to increase macro variable length.

manojinpec — Thu, 29 Mar 2012 11:38:47 GMT

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.

How to increase macro variable length.

LinusH — Thu, 29 Mar 2012 13:06:43 GMT

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

How to increase macro variable length.

kuridisanjeev — Thu, 29 Mar 2012 13:12:45 GMT

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

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

to="&email";

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

Re: How to increase macro variable length.

Tom — Thu, 29 Mar 2012 13:41:27 GMT

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;

Re: How to increase macro variable length.

data_null__ — Thu, 29 Mar 2012 13:47:04 GMT

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

Re: How to increase macro variable length.

kuridisanjeev — Thu, 29 Mar 2012 15:17:41 GMT

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.

How to increase macro variable length.

kuridisanjeev — Thu, 29 Mar 2012 15:24:20 GMT

Not sure I understand what you are trying say.

Can you please explain..??

How to increase macro variable length.

Tom — Thu, 29 Mar 2012 15:59:09 GMT

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.

How to increase macro variable length.

Reeza — Thu, 29 Mar 2012 16:13:58 GMT

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;

How to increase macro variable length.

Ksharp — Fri, 30 Mar 2012 02:47:10 GMT

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