Date:         Tue, 28 Dec 2004 12:55:41 -0500
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: Big Table / Little Table Update
Comments: To: Steve James <SPJ1@CDC.GOV>

How about data view ?

data Big;
input ID Date $ Flag;
cards;
1  12/1   1
1  12/1   1
1  11/31  0
2  12/1   0
2  12/3   1
;

data Incoming;
input ID Date $;
cards;
1  12/2
1  12/3
2  12/2
2  12/4
;

data bigv / view=bigv;
 set big incoming (in=a_);
by id;
if a_ then flag=1; else flag=0;
run;

proc print data=bigv;
run;

 ID    Date     Flag

  1    12/1       0
  1    12/1       0
  1    11/31      0
  1    12/2       1
  1    12/3       1
  2    12/1       0
  2    12/3       0
  2    12/2       1
  2    12/4       1

Not sure if update can do this kind of thing. I don't feel it is feasible
since you are actually adding records. Howard can certainly answer this.

Kind regards,

Ya Huang





On Tue, 28 Dec 2004 11:09:01 -0500, James, Steve <SPJ1@CDC.GOV> wrote:

>
>
>SAS-L
>
>
>
>We have a problem that I'd like your help on.  We have a huge data set
>which needs to be updated daily with patient records.  Each record has a
>patient ID and date of visit.  Thus, there are multiple occurrences of
>both patient ID and date of visit.  What we want to do is to add the
>incoming records to the existing data and then set a flag to designate
>which records are from the most recent visit.  For example:
>
>
>
>
>
>Big_Table
>
>
>
>ID         Date     Flag
>
>1          12/1      1
>
>1          12/1      1
>
>1          11/31    0
>
>2          12/1      0
>
>2          12/3      1
>
>etc....
>
>
>
>Incoming Table
>
>
>
>ID         Date
>
>1          12/2
>
>1          12/3
>
>2          12/2
>
>2          12/4
>
>
>
>Will result in the following
>
>
>
>1          12/1      0
>
>1          12/1      0
>
>1          11/31    0
>
>1          12/2       0
>
>1          12/3      1
>
>2          12/1      0
>
>2          12/3      0
>
>2          12/2      0
>
>2          12/4      1
>
>etc...
>
>
>
>The main idea is not to have to re-create the big table each time it's
>updated since it takes hours to do.  Any thoughts on how to do this will
>be most appreciated.
>
>
>
>Steve James
>
>Centers for Disease Control and Prevention
>
>sjames@cdc.gov
>
>
>
>
>
>