Date:         Wed, 25 Oct 2006 04:35:16 -0400
Reply-To:     Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Subject:      Re: Calculate an average between two dates
Comments: To: Craig Hansen <C.Hansen@UQ.EDU.AU>

Hi Craig,

First of all I corrected your second input program, that did not read the
second date correctly. A solution is:

data tempdates;
  input date date7. temp;
  format date date7.;
  datalines;
01jan01 24
02jan01 20
03jan01 19
04jan01 12
05jan01 24
06jan01 12
07jan01 15
08jan01 15
09jan01 23
10jan01 30
;
run;

data exposure;
  input id date1 date7. date2 : date7.; * Corrected statement;
  format date1 date2 date7.;
  datalines;
1 01jan01 10jan01
2 03jan01 07jan01
3 04jan01 10jan01
4 05jan01 09jan01
;
run;

/*PROC PRINT DATA=exposure; RUN;*/

DATA TempFmt;
  SET tempdates;
  Start = PUT (Date, BEST12.); End = Start;
  Label = PUT (temp, 2.);
  Type = 'N';
  FmtName = '_Temp';
RUN;

PROC FORMAT CNTLIN=TempFmt;

DATA Averages (DROP=I);
  SET exposure;
  Temp = 0;
  DO I = Date1 TO Date2;
    Temp + INPUT ( PUT ( I, _Temp. ), 2. ); * implicit RETAIN;
  END;
  Temp = Temp / (Date2-Date1+1);
RUN;

PROC PRINT DATA=Averages; RUN;

As said this is _a_ solution; thousands of different solutions possible.

Regards - Jim.
--
Jim Groeneveld, Netherlands
Statistician, SAS consultant
home.hccnet.nl/jim.groeneveld


On Wed, 25 Oct 2006 17:03:37 +1000, Craig Hansen <C.Hansen@UQ.EDU.AU> wrote:

>Dear All
>
>I have a time series of daily temperature readings ('tempdates' dataset
>below) and I need to find two dates within the time series and calculate
>the average temperature between those dates. However, the two dates I
>need to find are days of exposure for people, which are in another
>dataset ('exposure' dateset below).
>
>For example, based on the data below, for the person with id=1 in the
>exposure dataset their dates of exposure are between 01jan01 to 10jan01
>(inclusive), so I need to match those dates in the tempdates dataset and
>then calculate the average temp between those days - I need that in a
>new variable called 'avexposure'. So the average exposure for id=1 would
>be 19.4. Person id=2 has exposure dates 03jan01 to 07jan01 and their
>average exposure would be 16.4.
>
>I also need to calculate the average temperature not only for the entire
>exposure period but also for particular days within the exposure period,
>for example only the first three days, or first four days etc. For
>example, the average exposure for the for the first three days for
>person id=1 would be 21.
>
>Here is a simplified dataset:
>
>data tempdates;
>input date date7. temp;
>format date date7.;
>datalines;
>01jan01 24
>02jan01 20
>03jan01 19
>04jan01 12
>05jan01 24
>06jan01 12
>07jan01 15
>08jan01 15
>09jan01 23
>10jan01 30
>;
>run;
>
>data exposure;
>input id date1 date7. date2 date7.;
>format date1 date2 date7.;
>datalines;
>1 01jan01 10jan01
>2 03jan01 07jan01
>3 04jan01 10jan01
>4 05jan01 09jan01
>;
>run;
>
>Thanks in advance for any assistance
>
>Craig