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 (November 2006, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Wed, 22 Nov 2006 23:11:31 -0500
Reply-To:   "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:   Re: Looking for Year to Date Calculation Performance Improvements

On Tue, 21 Nov 2006 12:23:35 -0800, Tony Gallegly <tony.gallegly@GMAIL.COM> wrote:

>SAS-L, > >I am looking for a better way from a performance and memory usage >standpoint to accomplish the following. > >I need to calculate a year to date average for each observation. The >challenge is I have a minimum of 100,000 observations.

What is maximum?

Spread out over how many years?

More or less evenly distributed within each year?

> >Existing Code >PROC SQL _METHOD BUFFERSIZE=1000000; > CREATE TABLE AGGREGATE AS > SELECT A.COST > ,A.YEAR > ,A.PERIOD > ,A.WEEK > ,A.DAY > ,AVG(B.SCORE) AS AVG_SCORE > FROM ZETA A, > ZETA B > WHERE A.YEAR = &CURRENT_YEAR > AND B.YEAR = A.YEAR > AND B.PERIOD <= A.PERIOD > AND B.WEEK <= A.WEEK > AND B.DAY <= A.DAY > GROUP BY A.COST, A.YEAR, A.PERIOD, A.WEEK, A.DAY; >QUIT;

What are the PERIODs, what convention is used for weeks, and how do weeks, periods, and years nest? Or, put differently, how can we derive day numbers relative to years (eg, Feb. 2 is the 33rd day of the year)?

> >Sample Data: The avg_score field is the result of the above sql >statement. >COST YEAR PERIOD WEEK DAY SCORE AVG_SCORE >301020 2006 1 1 1 91 91.00 >301020 2006 1 1 3 100 95.50 >301020 2006 1 1 7 100 97.00 >301020 2006 1 2 2 100 97.75 >301020 2006 1 2 4 100 98.20 >301020 2006 1 2 4 100 98.50 >301020 2006 1 2 6 100 98.71 >301020 2006 1 3 3 100 98.88 >301020 2006 1 3 5 92 98.11 >301020 2006 1 4 2 100 98.30 >301020 2006 1 4 3 100 98.45 >301020 2006 2 1 1 100 98.58 >301020 2006 2 1 3 100 98.69 >301020 2006 2 1 5 100 98.79 >301020 2006 2 2 2 100 98.87 >301020 2006 2 2 2 100 98.94 >301020 2006 2 2 4 100 99.00 >301020 2006 2 3 1 100 99.06 >301020 2006 2 3 1 90 98.58 >301020 2006 2 3 5 100 98.65 >301020 2006 2 4 2 100 98.71 >301020 2006 2 4 4 100 98.77 >301020 2006 2 4 6 92 98.48 >301020 2006 3 1 2 82 97.79 >301020 2006 3 1 2 100 97.88 > >Thanks in advance, >Tony


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