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.