For that data volume and performance need, I would reccomend looking at the cost of installing a SPD Server. Not only has it the capacity to improve data access dramatically, it also has a simple form of syntax for such an update (IIRC) proc append data= <transaction_table> out= <wide_table> ( uniquesave= rep ); run; The effect is that matching rows in the wide_table are replaced and new rows are appended. I think that was what was wanted.

Perhaps the sas9 SPD library engine might support similar processing without needing all the hardware normal for SPD Server.

Good Luck Peter Crawford Crawford Software Consultancy Limited

On Thu, 13 May 2004 18:37:03 -0700, Wing-Sze Tsui <wingsze_tsui@HOTMAIL.COM> wrote:

>Thanks Richard. >The tables are a mix of numeric and characters. And I'm currently using >Data Step with Modify statement and the KEY= option, with index on the >master dataset. I've tried data step merge and the performance is not good >neither. And also Proc SQL Joining and Updates, but the performance even >worse. And I cannot just use Proc Append as the transaction dataset has not >only new data that need to be append to the master dataset, but also some >updates to the existing master dataset. > >I can't think of anyother way... please help! > >Oh, Thanks again for the reminder. I'll unsubscribe and resubscribe again. > >Thanks a lot! > >Wing. > >>From: "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM> >>Reply-To: "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM> >>To: SAS-L@LISTSERV.UGA.EDU >>Subject: Re: Updating a wide dataset >>Date: Thu, 13 May 2004 10:55:50 -0400 >> >><wingsze_tsui@HOTMAIL.COM> wrote in message >>news:200405130130.i4D1UgG01377@listserv.cc.uga.edu... >> > Hi, >> > >> > I'm trying to update a wide table (with 1970 columns and 1,500,000 rows >>and >> > a size of ~ 24G) with a transaction dataset (with 300 columns and >>1,300,000 >> > rows and a size of ~7G). The master dataset already have index and the >> > transaction dataset is sorted. >> > >> > I've tried to use data step modify and it took ~2.5 hrs on a Windows2000 >> > Server with 4G RAM. Is there anyway I can improve the performance? >> > >> > HELP! >> > >> > Thanks a lot in advance! >> > >> > Wing. >> >>Wing: >> >>What is the nature of the columns ? All numeric ? wide characters ? >>Perhaps you could post the first portion of the output of PROC CONTENTS. >> >>What is the nature of the update ? Is it a data step with an UPDATE >>statement, a MODIFY statement or a SET statement? Are you trying to Proc >>APPEND ? >> >>You might also unsubscribing and resubscribing with a 'fixed' name. Your >>subscription name is "SUBSCRIBE SAS-L WingSze" which can causes newsgroup >>replies to your questions to be rejected by the listserv. >> >>-- >>Richard A. DeVenezia >>http://www.devenezia.com/downloads/sas/samples > >_________________________________________________________________ >MSN Premium: Up to 11 personalized e-mail addresses and 2 months FREE* >http://join.msn.com/?pgmarket=en- ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_M SNIS_Taglines