LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (December 2004, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 28 Dec 2004 12:02:22 -0600
Reply-To:     "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Subject:      Re: Big Table / Little Table Update
Comments: To: "James, Steve" <SPJ1@CDC.GOV>
Content-Type: text/plain; charset="iso-8859-1"

Steve, I am not currently seeing a easy way to do what you want without cycling through the big data set atleast once so the flag variable can be repopulated with zeros. Perhaps Howard has a cool update trick that would do the job here. As I see it you have basically two ways to go about doing this: 1st: data bigfile: set bigfile (in = big) smallfile (in = small); by id: if big then flag = 0; else if small then flag = 1; run; 2nd: data bigfile: set bigfile (in = big) smallfile (in = small); by id: if small then flag = date(); run; here you will have the current data time stamp and instead of selecting a flag of 1 or 0 you would use the most current data to select those records that came from the most recent update. HTH Toby Dunn

________________________________

From: SAS(r) Discussion on behalf of James, Steve Sent: Tue 12/28/2004 10:09 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Big Table / Little Table Update

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


Back to: Top of message | Previous page | Main SAS-L page