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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.