LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (August 2007, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 28 Aug 2007 12:05:37 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: ERROR: Overflow has occurred; evaluation is terminated.
Content-Type: text/plain; charset="iso-8859-1"

Hi, Rangoon,

This isn't tested- but hopefully will give you the idea.

You could create a table with your patient numbers rather than put it into a macro variable, then left outer join on that table with your other tables in your macro statement.

PROC sql noprint; create table work.temp_patients as select distinct ptno into: pt separated by ',' from xx where ppp in('multi') ; /*where value keeps changing*/ quit;

Then in your macro, you would left outer join by the temp table as well as the other 2 tables (i.e. join 3 tables, not 2)

proc sql; create table work.newtable as select gg.*,bb.* from work.temp_patients left outer join work.gg left outer join work.bb on temp_patients.ptno=gg.ptno and gg.study=bb.study and gg.ptno=bb.ptno;

-Mary

----- Original Message ----- From: rangoonraja123@GMAIL.COM To: SAS-L@LISTSERV.UGA.EDU Sent: Tuesday, August 28, 2007 11:31 AM Subject: Re: ERROR: Overflow has occurred; evaluation is terminated.

On Aug 28, 11:50 am, gerhard.hellrie...@T-ONLINE.DE (Gerhard Hellriegel) wrote: > There is for sure a way! You don't can overcome the limits which are > documented, but you can use 20 macro varoables, or 20,000 ans store only 1 > num in each! But that is not the thing, macro variables or even the macro > facility is designed for! > If you handle 20,000 macro variables, you want have response times any more, > but delivery times instead and also memory problems. If you deal with a > dataset with 20,000 obs (which SAS is designed for) you nearly can't measure > the response times! > > So once again: tell us what you want to do and there is for sure a more > suitable solution than holding data in macro variables! > By the way: if you think of "transporting information from one step to the > next", that is exactly something where a sas dataset is used for! > > Gerhard > > On Tue, 28 Aug 2007 08:24:59 -0700, rangoonraja...@GMAIL.COM wrote: > >On Aug 28, 10:44 am, nos...@HOWLES.COM ("Howard Schreier <hs AT dc-sug > >DOT org>") wrote: > >> Seehttp://support.sas.com/techsup/unotes/SN/005/005178.html > > >> On Tue, 28 Aug 2007 07:34:35 -0700, rangoonraja...@GMAIL.COM wrote: > >> >Hi all, > > >> >i am having following error in my log > > >> >ERROR: Overflow has occurred; evaluation is terminated. > > >> >PROC sql noprint; > >> > select distinct ptno into: pt separated by ',' > >> > from xx > >> > where yy in('multi') ; > >> >quit; > > >> >This happens when i use this macro variable(pt) from sql in another > >> >program > >> >this macro variable (pt) has a value of 20,000 patient numbers > > >> >(eg: 2001, 2002, > > >2003,............................................................................... > >> >and so on). > > >> >I need a have a macro variable containing the patient numbers. > > >> >how do i over come this. > > >> >regards, > >> >rangoon. > > >hi howard, > > >Is there any way that we can overcome this. > > >Thank you > > >___rangoon

Thank you all for your inputs.

the reason why i need a macro variable for ptno is the value for "p " keeps changing. following is a part of large "macro de". but if this is overcome then everything will be fine.

%macro de(ano = , p = );

PROC SORT DATA = g OUT = gg; BY study ptno; WHERE anal in(&ano); RUN;

DATA demo1; MERGE gg bb; BY study ptno; IF ptno in (&&&p); RUN;

%mend de;

PROC sql noprint; select distinct ptno into: pt separated by ',' from xx where ppp in('multi') ; /*where value keeps changing*/ quit;

PROC sql noprint; select distinct ptno into: tt separated by ',' from xx where ppp in('mint') ; /*where value keeps changing*/ quit;

%macro de(ano = 2, p = pt); /* p macro parameter value is changed*/

%macro de(ano = 2, p = tt); /* p macro parameter value is changed*/

Thank you, Rangoon.


Back to: Top of message | Previous page | Main SAS-L page