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
| 
