Date: Thu, 3 Jul 2008 10:03:39 -0500
Reply-To: "Raynaud, Scott" <Scott.Raynaud@TARGETBASE.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Raynaud, Scott" <Scott.Raynaud@TARGETBASE.COM>
Subject: Re: sort
Content-Type: text/plain; charset="us-ascii"
I looked at some stuff on indexes. My data look like this:
Name account_number day transaction_number other data(sales,
gross margin, ext)
First I rollup on transaction number getting sums of the other data.
Then, rollup by name, account_number and day (the combination of name
and account_number identifies a person and persons might have purchases
on different days), again getting sums along the way. This strategy
requires separate sorts for each rollup. The final file would look
something like this:
Name account_number sum_of_sales sum_of_gross_margin.....
Indexing was proposed as a method along the path to a solution to this
problem. One way to index would be to use proc datasets to set up a
composite index using transaction number, name, account number and day,
correct? The index statement would be:
index create x=(transaction_number name account_number day)
To do the first rollup, my standard code looks like this:
By transaction_number notsorted;
If first.transaction_number then do;
All this being said, I'm not sure where indexing gets me. Doesn't the
file still have to be sorted to use the by statement in the rollup? I
need to avoid sorting. Can someone enlighten me?
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Fehd, Ronald J. (CDC/CCHIS/NCPHI)
Sent: Monday, June 30, 2008 4:41 PM
Subject: Re: sort
> From: Scott Raynaud
> >Looked at some prior posts on this subject but I'm still a little
> >unclear. I run v 9.1.2 on a Unix box (don't have SAS
> Connect). I need
> >rollup a transactional file of several hundred million records. I'm
> >afraid the sort prior to the rollup will fail due to space and time
> >expense. Any ideas on the best alternative? I'm leaning toward
> >the file into several smaller ones like this one I saw on
> the listserv:
Based on my recent experience of combining several years of data
I suggest that you index the data.
The following posts contain code and times for:
algorithm: sort, and set
algorithm: append and index
Howard suggested that I do a view of the four years of data I have
and sort the view.
I have that on my todo list and will report the times when I get it
data Work.Four_Years /view;
end = EndoFile;
Proc Sort data = Work.Four_Years
out = Library.The_Four_Years;
by <the ID list>;
Ron Fehd the macro maven CDC Atlanta GA USA RJF2 at cdc dot gov
[ALERT] -- Access Manager: This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this e-mail in error, please immediately notify us by calling our North American Help Desk at (972)506-3939.
Targetbase Messaging Services provided by DMSP