Date:         Mon, 1 Dec 2003 16:21:40 -0500
Reply-To:     "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Subject:      Re: Overflow error with lag function
Comments: To: Cynthia qiu <cynqiu@YAHOO.COM>

Hi, Cynthia,

I am not exactly clear what you want to do. So I assume that you want to
calculate the difference between the date1 of the first record of an id,
and the date2 of the last record of an id. If that is the case the
following will do. I have cut down some of the extra details, to make the
code clearer. HTH

Cheers,
Chang

<sasl:code>
data test;
  input id @3 date1 date9. @13 date2 date9.;
datalines;
1 26MAR2002 01JUL2002
2 24MAY2002 14JUN2002
2 04JUN2002 29MAY2002
3 25APR2002 26APR2002
4 30JUL2002 12AUG2002
4 28MAY2002 14JUN2002
4 04JAN2002 25FEB2002
5 09MAR2002 27DEC2002
5 08OCT2002 10MAR2002
5 10MAR2002 05DEC2002
;
proc sort data =test;
    by id date1;
run;
data test2;
    set test;
    by id date1;
    retain firstDate lastDate .;
    if first.id then do;
        firstDate = date1;
        lastDate  = .;
    end;
    if last.id  then do;
        lastDate  = date2;
        if missing(firstDate) or missing(lastDate) then st2Ed = .;
        else st2Ed = lastDate - firstDate;
    end;
run;
proc print data=test2;
    var id firstDate lastDate st2Ed;
    format firstDate lastDate date.;
run;
/*
              first        last
Obs    id     Date         Date    st2Ed

  1     1    26MAR02    01JUL02      97
  2     2    24MAY02          .       .
  3     2    24MAY02    29MAY02       5
  4     3    25APR02    26APR02       1
  5     4    04JAN02          .       .
  6     4    04JAN02          .       .
  7     4    04JAN02    12AUG02     220
  8     5    09MAR02          .       .
  9     5    09MAR02          .       .
 10     5    09MAR02    10MAR02       1
*/
</sasl:code>


On Mon, 1 Dec 2003 14:38:34 -0500, Cynthia qiu <cynqiu@YAHOO.COM> wrote:

>Hi SAS-l'ers ,
>
>I am having problems with a program that I would like would return the
>number of days for a given id between two dates.  For example in the data
>below the program would calculate number of days between 26MAR2002 and
>01JUL2002 for ID=1 and the number of days between 24MAY2002 and 29MAY2002
>for ID= 2, etc.
>
>There are two problems with the program. First, it produces correct
results
>for some but not all of the IDs though it appears to me the code generated
>by SQL is okay. In other words, the lag value is correct though the
results
>are not.
>
>I know the lag function can produce unexpected results when used in a do
>loop or something similar but I dont think that's the problem here because
>the macro variable is not executed within a conditional statement.
>
>Second, when I run the program on a large data set I get the following
>error message:
>
>ERROR: Overflow has occurred; evaluation is terminated.
>ERROR: Overflow has occurred; evaluation is terminated.
>ERROR: Overflow has occurred; evaluation is terminated.
>ERROR: Out of memory.
>ERROR: Out of memory.
>ERROR: Out of memory.
>NOTE: The SAS System stopped processing this step because of errors.
>
>Can someone suggest any ideas how I might get this program to
>work properly?  Thank you.
>
>/********************************************************/
>
>
>data test ;
>input @1 id @3 date1 DATETIME22. @28 date2 DATETIME22. ;
>datalines;
>1 26MAR2002:00:00:00.000  01JUL2002:00:00:00.000
>2 24MAY2002:00:00:00.000  14JUN2002:00:00:00.000
>2 04JUN2002:00:00:00.000  29MAY2002:00:00:00.000
>3 25APR2002:00:00:00.000  26APR2002:00:00:00.000
>4 30JUL2002:00:00:00.000  12AUG2002:00:00:00.000
>4 28MAY2002:00:00:00.000  14JUN2002:00:00:00.000
>4 04JAN2002:00:00:00.000  25FEB2002:00:00:00.000
>5 09MAR2002:00:00:00.000  27DEC2002:00:00:00.000
>5 08OCT2002:00:00:00.000  10MAR2002:00:00:00.000
>5 10MAR2002:00:00:00.000  05DEC2002:00:00:00.000
>;
>
>        proc sort data =test ;
>   by id date1 ;
> run;
>
> Data test2   ;
> Set test;
>  By id  ;
>  If first.id then ct = 0 ;
>  ct+1 ;
>  lag = compress( "lag"||ct-1 ) ;
> Run ;
>
> proc sql ;
> select  "StatusDates="||compress( "intck('day', "
>  ||lag||"("||("datepart(date1))")
>  ||",datepart(date2));")
> into: dates
> separated by " "
> from  test2 ;
> Quit ;
>
> proc sort data = test2 ;
>          by id date1 ;
> run ;
>
> data final  ;
> set test2   ;
> by id date1 ;
> &dates
> if last.id then
>    st2Ed = StatusDates ;
> if first.id and last.id then
>    st2Ed = intck ('day', datepart(date1),datepart(date2));
> run ;
>
> proc print data = final noobs ;
> var id date1 date2   st2Ed ;
> format date1 date2 DATETIME22. ;
> run ;
>
>id                     date1                     date2    st2Ed
>
>1 26MAR2002:00:00:00        01JUL2002:00:00:00      97
>2 24MAY2002:00:00:00        14JUN2002:00:00:00       .
>2 04JUN2002:00:00:00        29MAY2002:00:00:00      64    <===wrong result
>3 25APR2002:00:00:00        26APR2002:00:00:00       1
>4 04JAN2002:00:00:00        25FEB2002:00:00:00       .
>4 28MAY2002:00:00:00        14JUN2002:00:00:00       .
>4 30JUL2002:00:00:00        12AUG2002:00:00:00     220
>5 09MAR2002:00:00:00        27DEC2002:00:00:00       .
>5 10MAR2002:00:00:00        05DEC2002:00:00:00       .
>5 08OCT2002:00:00:00        10MAR2002:00:00:00       1