Hello everyone,
I am having trouble extracting the first and last timestamp for the single encounter who was admitted in different ICUs in the same admission from the data below. I am sorting data on id and ICU and using proc summary to extract the first and last time stamp but sorting data on id and icu is not what i want. i am getting first timestamp for ICU 1 29NOV12:14:06:00 and last tmestamp i am getting is 04DEC12:14:06:00 which i donot want. I want first and last timesamp for individual ICU no matter if it is in the same ICU. Any input would be appreciated.
Thanks
data icustay;
infile datalines missover;
input id ts : datetime. ICU ;
format ts datetime.;
datalines;
5307 29NOV12:14:06:00 1
5307 29NOV12:15:45:00 1
5307 30NOV12:17:00:00 1
5307 30NOV12:20:06:00 1
5307 01DEC12:14:06:00 2
5307 01DEC12:20:16:00 2
5307 02DEC12:21:06:00 2
5307 03DEC12:14:06:00 1
5307 03DEC12:20:06:00 1
5307 04DEC12:14:06:00 1
;
run;
proc print data=work.icustay;
run;
There are quite a few topics on this here. Its simple process:
data want; set icustay; by id icu notsorted; if first.icu or last.icu then output; run;
This goes through your data as it is, outputting first and last of each icu category.
Please post an example of your expected output data. 04DEC12:14:06:00 IS the maximum timestamp, for all ICU's and for ICU 1, so I don't see why you would not want that to be the result.
Hi KurtBremser, below is the example of the output i want.
Thanks for yoyur reply
id ts unit first last
5307 29NOV12:14:06:00 1 29NOV12:14:06:00 30NOV12:20:06:00
5307 29NOV12:15:45:00 1 29NOV12:14:06:00 30NOV12:20:06:00
5307 30NOV12:17:00:00 1 29NOV12:14:06:00 30NOV12:20:06:00
5307 30NOV12:20:06:00 1 29NOV12:14:06:00 30NOV12:20:06:00
5307 01DEC12:14:06:00 2 01DEC12:14:06:00 02DEC12:21:06:00
5307 01DEC12:20:16:00 2 01DEC12:14:06:00 02DEC12:21:06:00
5307 02DEC12:21:06:00 2 01DEC12:14:06:00 02DEC12:21:06:00
5307 03DEC12:14:06:00 1 03DEC12:14:06:00 04DEC12:14:06:00
5307 03DEC12:20:06:00 1 03DEC12:14:06:00 04DEC12:14:06:00
5307 04DEC12:14:06:00 1 03DEC12:14:06:00 04DEC12:14:06:00
This is how you get the min/max per individual ICU stay:
data want (keep=id icu mints maxts);
set icustay;
by id icu notsorted;
retain mints maxts;
format mints maxts datetime.;
if first.icu then mints = ts;
if last.icu
then do;
maxts = ts;
output;
end;
run;
assuming that the dataset is correctly sorted by id and ts.
If you want to merge back to get the expanded output you posted, you would need to create a "stay identifier" first along the by values I used.
Thank you very much for your help. can you please explain where i need to create the identifier you are saying to merge with the output i posted
@AMFR wrote:
Thank you very much for your help. can you please explain where i need to create the identifier you are saying to merge with the output i posted
This would look like that:
proc sort data=icustay;
by id ts;
run;
/* just to make sure for larger datasets */
data intermediate;
set icustay;
by id icu notsorted;
retain staycount 0;
if first.icu then staycount + 1;
run;
data minmax (keep=staycount mints maxts);
set intermediate;
by staycount;
retain mints maxts;
format mints maxts datetime.;
if first.staycount then mints = ts;
if last.staycount
then do;
maxts = ts;
output;
end;
run;
data result;
merge intermediate minmax;
by staycount;
drop staycount;
run;
This is eaxctly what I was looking for. You are amzing. Thank you very much
There are quite a few topics on this here. Its simple process:
data want; set icustay; by id icu notsorted; if first.icu or last.icu then output; run;
This goes through your data as it is, outputting first and last of each icu category.
Thank you very much RW9. i highly appreciate your help.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.