How to extarct first and last timestamp

[AMFR]

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;

　

[Kurt_Bremser]

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.

[AMFR]

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

[Kurt_Bremser]

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.

[AMFR]

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

[Kurt_Bremser]

---

@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;

[AMFR]

This is eaxctly what I was looking for. You are amzing. Thank you very much

[RW9 · Accepted solution]

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.

[AMFR]

Thank you very much RW9. i highly appreciate your help.