Data Logic

[Almoha]

All,

i have the following data

ID  time   flag    res  

1    2              20

1    4      Y       02

1    6              05

1    8              30

1    10             20

1    12             04

1    14             06   

i am trying to insert a record at each time point (time) with minimum result(res) from all the records that are after the record that is flagged Y but before the time point(time) for which we

are inserting records under.Here is the sample output that am trying to get to.note that i pasted just one 1 ID as an example,

ID  time   flag    res    recordtyp

1    2              20

1    4      Y      02

1    6              05

1    8              30

1    6              05     New          The res=5 is minimum value before time=8 and from records that are after the record that is flagged as Y

1    10             20

1    6              05     New          The res=5 is minimum value before time=10 and from records that are after the record that that is flagged as Y

1    12             04

1    6              05     New          The res=5 is minimum value before time=12 and from records that are after the record that that is flagged as Y

1    14             06   

1    12             04     New          The res= 4 is minimum value before time=12 and from records that are after the record that that is flagged as Y

[RW9]

Well, that was a bit of a nightmare.  The below should work though you will need a by id and reset after each block as I have only done for one id.

data have;
  attrib ID time res format=best. flag format=$2.;
  infile datalines dlm=',' dsd missover;
  input id time flag $ res;
datalines;
1,2,,20
1,4,Y,02
1,6,,05
1,8,,30
1,10,,20
1,12,,04
1,14,,06   
;
run;

data want (keep=id new_time flag new_res recordtyp);
  set have;
  retain min_res min_time flagon;
  attrib new_time new_res format=best.;
  new_time=time;
  new_res=res;
  output;
  if flagon="Y" then do;
    if time in (8,10,12,14) then do;
      new_time=min_time;
      new_res=min_res;
      recordtyp="New";
      output;
    end;
    if res <= min_res or min_res=. then do;
      min_res=res;
      min_time=time;
    end;
  end;
  if flag="Y" then flagon="Y";
run;