Date:         Thu, 18 Oct 2007 01:49:02 -0400
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: strategy suggestions wanted ... alternatives to merging on
              look-aheads
Comments: To: reply@TO-GROUP.NFO

data d1;
input time : time. temp;
cards;
17:00:00    45.1
17:01:00    44.9
17:02:00    44.5
17:03:00    43.2
17:04:00    46.1
17:05:00    45.6
;

data d2;
input time : time. widget $ length :time.;
cards;
17:00:10    ABC123    00:00:20
17:00:30    ABC124    00:00:45
17:01:15    ABC125    00:00:50
17:02:05    ABC126    00:00:25
17:02:30    ABC127    00:00:30
17:03:00    ABC128    00:02:30
;

** expand d2, so that each second will have a records;
data d3;
 set d2 (rename=(time=time0));
 do time=time0 to time0+length0-1;
 output;
 end;
run;

** merge with d1 by time, then LOCF the temp to fill in the blank;
data d12;
 merge d1 d3;
by time;
retain temp_t;
if ^missing(temp) then temp_t=temp;
else temp=temp_t;
if widget ^='';
run;

** now the weighted temp is basically a mean of temp for that widget;

proc sql;
select distinct min(time) as time format=time.,
       widget, mean(temp) as weighted_temp
from d12
group by widget
order by widget, time
;

                       weighted_
    time  widget            temp
---------------------------------
17:00:10  ABC123            45.1
17:00:30  ABC124        45.03043
17:01:15  ABC125        44.85294
17:02:05  ABC126            44.5
17:02:30  ABC127        44.45806
17:03:00  ABC128        44.84503

min(time) to get the starting time for that widget.

HTH

Ya

On Thu, 18 Oct 2007 03:34:52 GMT, theorbo <reply@TO-GROUP.NFO> wrote:

>Warning & Disclaimer: i am at home have not included any executable code in
>this question. While code is great, I am mainly looking for ideas. Thanks!
>_____________________________________________
>
>What strategy would you use to tackle this problem ...
>
>I have two sources of data that related to measuring the temperature and
>exposure time of widgets on an assembly line ...
>
>1) Temperature Readings taken each minute
>2) The length of time (in seconds) that different widgets are exposed to
>temperature at time X
>
>It's like I'm traveling a time-line and monitoring two things ... what is
>the temperature and how long is a certain widget exposed. Most of the
>widgets are exposed for less than a minute but many of the exposure
>durations span two temperature readings. For these cases I determine the
>length of time that the widget was exposed to each temperature reading and
>create a weighted average.
>
>For instance, here are the two data sources.
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>TEMPERATURE READINGS
>   time        temp
>17:00:00    45.1
>17:01:00    44.9
>17:02:00    44.5
>17:03:00    43.2
>17:04:00    46.1
>17:05:00    45.6
>etc ...
>
>WIDGET EXPOSURE READINGS
>   time        widget        length of exposure
>17:00:10    ABC123    00:00:20
>17:00:30    ABC124    00:00:45
>17:01:15    ABC125    00:00:50
>17:02:05    ABC126    00:00:25
>17:02:30    ABC127    00:00:30
>17:03:00    ABC128    etc. etc.
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>I've merged the data so that i know the temperature at the exposure start
>time and then merged-on the temperature of the following minute to cover
>those cases where I need to weight the values. I also calculate the percent
>of time in each minute ... resulting in this dataset.
>
>    time        widget        length        % min1    % min2  temp1
>next_temp   weighted_temp
>17:00:10    ABC123    00:00:20    1.00        0.00       45.1        45.1
>45.1
>17:00:30    ABC124    00:00:45    0.67        0.33       45.1        44.9
>45.03
>17:01:15    ABC125    00:00:50    0.90        0.10       44.9        44.5
>44.86
>17:02:05    ABC126    00:00:25    1.00        0.00       44.5        43.2
>44.5
>17:02:30    ABC127    00:00:30    1.00        0.00       44.5        43.2
>44.5
>
>Weighted_temp = (% min1 * start_temp) + (% min2 * next_temp)
>
>What I didn't account for are those widgets that are exposed a length of
>time greater than 60 seconds - spanning three (or more) minutes.
>
>EXPOSURE READING:
>time= 17:03:00
>widget= ABC128
>length of exposure=00:02:30 ...
>
>exposed during 17:03, 17:04, & part of 17:05
>so weighted temp for widget ABC128 =
>(0.4 * 43.2) + (0.4 * 46.1) + (0.2 * 45.6) = 45.84 .
>
>I am not sure about how to best create this weighted_temp to account for an
>unknown duration length.
>
>I was toying with creating an end_time or using some kind of remainder of
>exposure time after each minute. I don't want to merge on lots of look
>aheads because that seems limited and would only account for the instances
>that work with the number of read-aheads (of temp) that I merge on.
>
>Any suggestions?