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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.