|Date: ||Fri, 14 May 2004 04:34:45 -0400|
|Reply-To: ||Peter Crawford <peter@CRAWFORDSOFTWARE.DEMON.CO.UK>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Peter Crawford <peter@CRAWFORDSOFTWARE.DEMON.CO.UK>|
|Subject: ||Re: Updating a wide dataset|
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 );
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.
Crawford Software Consultancy Limited
On Thu, 13 May 2004 18:37:03 -0700, Wing-Sze Tsui
>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
>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
>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
>Thanks a lot!
>>From: "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
>>Reply-To: "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
>>Subject: Re: Updating a wide dataset
>>Date: Thu, 13 May 2004 10:55:50 -0400
>><wingsze_tsui@HOTMAIL.COM> wrote in message
>> > Hi,
>> > I'm trying to update a wide table (with 1970 columns and 1,500,000
>> > a size of ~ 24G) with a transaction dataset (with 300 columns and
>> > rows and a size of ~7G). The master dataset already have index and
>> > transaction dataset is sorted.
>> > I've tried to use data step modify and it took ~2.5 hrs on a
>> > Server with 4G RAM. Is there anyway I can improve the performance?
>> > HELP!
>> > Thanks a lot in advance!
>> > 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
>>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
>MSN Premium: Up to 11 personalized e-mail addresses and 2 months FREE*