Date:         Mon, 11 Dec 2006 06:45:09 -0800
Reply-To:     brodkorbtd <Tyson.D.Brodkorb@WELLSFARGO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         brodkorbtd <Tyson.D.Brodkorb@WELLSFARGO.COM>
Organization: http://groups.google.com
Subject:      Update a table on itself
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset="iso-8859-1"

I have a table, Work.master, that I insert new records into every week
and I need to recalculate the Month to Date Gain, MTDGain.  Currently I
am using proc sql to join the table on itself to calculate this, but
the problem with that is I lose my indexes and constraints.  There has
to be a better way to do it, but I'm at a loss.

Here is an example of what I'm trying to do.  The first three
statements produce a simple example of my current data, and the final
proc sql is what I'm using to update the MTDGain.  Any help on doing
this more efficient, and so that I don't lose my indexes would be
greatly appreciated.

Thank you,

/*****************************************************************************************************************/
data master;

    format Branch $4.
           Month MMDDYY10.
           MonthEndBalance
           MTDGain Dollar12.2;

    input Branch $4. +1 Month MMDDYY10. +1 MonthEndBalance 4.;
    datalines;
0002 01-01-2006 1897
0002 02-01-2006 2923
0002 03-01-2006 3823
0002 04-01-2006 4129
0002 05-01-2006 8145
0999 01-01-2006 2000
0999 02-01-2006 2123
0999 03-01-2006 4567
0999 04-01-2006 5423
0999 05-01-2006 6923
    ;

run;

proc sort data = Work.Master;
    by Branch Month;
run;

proc datasets library = work nolist;
    modify master;

    index create pk_Bnch_Mth = (Branch Month) / unique;
run;

/****************************** Statement I'm using to update my
MTDGain *******************************/
proc sql noprint;
    create table Work.Master as
    select Orig.*
           ,Case when Missing(Prv.MonthEndBalance) = 1 then
Orig.MonthEndBalance
                 else Orig.MonthEndBalance - Prv.MonthEndBalance
            end Format Dollar12.2 as MTDGain
    from   Work.Master (drop = MTDGain) Orig
           left outer join Work.Master Prv
                         on Prv.Branch = Orig.Branch
                         and intnx("month", Prv.Month, 1) = Orig.Month
    order by Orig.Branch
             ,Orig.Month;
quit;