Date:         Mon, 11 Dec 2006 15:13:34 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Update a table on itself
Comments: To: Tyson.D.Brodkorb@WELLSFARGO.COM
In-Reply-To:  <1165848309.024060.38060@n67g2000cwd.googlegroups.com>
Content-Type: text/plain; format=flowed

Do a data step update, should preserve your index on Master.  If you need
syntax help look up "Howard and Update" in the SAS-L archives and/or SUGI
proceedings.



Toby Dunn

To sensible men, every day is a day of reckoning. ~John W. Gardner

The important thing is this: To be able at any moment to sacrifice that
which we are for what we could become. ~Charles DuBois

Don't get your knickers in a knot. Nothing is solved and it just makes you
walk funny. ~Kathryn Carpenter






From: brodkorbtd <Tyson.D.Brodkorb@WELLSFARGO.COM>
Reply-To: brodkorbtd <Tyson.D.Brodkorb@WELLSFARGO.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Update a table on itself
Date: Mon, 11 Dec 2006 06:45:09 -0800

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;

_________________________________________________________________
WIN up to $10,000 in cash or prizes – enter the Microsoft Office Live
Sweepstakes http://clk.atdmt.com/MRT/go/aub0050001581mrt/direct/01/