Date:         Wed, 29 May 2002 18:23:37 -0400
Reply-To:     "Huang, Ya" <ya.huang@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <ya.huang@PFIZER.COM>
Subject:      Re: Avg subsets of values in a dataset
Comments: To: Gigi Lipori <pflugg@BELLSOUTH.NET>
Content-Type: text/plain

Gigi,

Since you have gaps in the date, we need to fill in the
gaps first. The way to fill in the missing date, is to
generate an index data set with one variable date, starts from
the first date in your data, and end at the last date in your
data. Then merge the index data set with your original
data, and set missing value to zero. In this step, a week
number is also generated for grouping. Finally use proc sql
to summarize the data:

data xx;
input date : mmddyy8. value :best.;
format date date.;
cards;
1/1/01 55
1/3/01 58
1/4/01 59
1/6/01 57
1/7/01 56
1/8/01 72
1/9/01 67
1/10/01 45
1/11/01 75
1/12/01 98
1/13/01 87
1/14/01 77
1/15/01 65
;

proc sql noprint;
select min(date), max(date)
 into :begin, :end
from xx
;

data allday;
 do date=&begin to &end;
 output;
 end;
 run;

data xx;
 merge xx allday;
 by date;
 if value=. then value=0;
 wk=ceil((date-&begin+1)/7);
 run;

proc print;

proc sql;
select distinct wk,
  min(date) as start format=date.,
  max(date) as stop format=date.,
  mean(value) as avg format=8.3,
  min(value) as low format=8.3,
  max(value) as high format=8.3
from xx
group by wk
;
run;

-----------------------
      wk    start     stop       avg       low      high
----------------------------------------------------------
       1  01JAN01  07JAN01    40.714     0.000    59.000
       2  08JAN01  14JAN01    74.429    45.000    98.000
       3  15JAN01  15JAN01    65.000    65.000    65.000

Kind regards,

Ya Huang

-----Original Message-----
From: Gigi Lipori [mailto:pflugg@BELLSOUTH.NET]
Sent: Wednesday, May 29, 2002 2:39 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Avg subsets of values in a dataset


If I have a dataset that looks something like the following:

date     value
1/1/01 55
1/3/01 58
1/4/01 59
1/6/01 57
1/7/01 56
1/8/01 72
...
continue same thing for four or five years.

What's the easiest way to grab the 'weekly' averages, as well as highs and
lows for the week. For instance....Week 1/1 through 1/7, I would want to
output a record something like 1/1/01 1/7/01 41 0 59 (consider missing
values to be 0 -- format start day, end day, avg, low, high). Then I'd want
it to pick up for the next 7 days, and so forth.

I know how to do it in VB, but I have trouble following iterations in SAS.
Any help would be appreciated...Gigi