topic Re: How to extarct first and last timestamp in SAS Programming

How to extarct first and last timestamp

AMFR — Thu, 11 May 2017 12:36:17 GMT

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;

Re: How to extarct first and last timestamp

Kurt_Bremser — Thu, 11 May 2017 12:50:52 GMT

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.

Re: How to extarct first and last timestamp

RW9 — Thu, 11 May 2017 12:53:00 GMT

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.

Re: How to extarct first and last timestamp

AMFR — Thu, 11 May 2017 12:56:16 GMT

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

Re: How to extarct first and last timestamp

AMFR — Thu, 11 May 2017 13:16:43 GMT

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

Re: How to extarct first and last timestamp

Kurt_Bremser — Thu, 11 May 2017 13:17:25 GMT

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.

Re: How to extarct first and last timestamp

AMFR — Thu, 11 May 2017 13:23:32 GMT

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

Re: How to extarct first and last timestamp

Kurt_Bremser — Thu, 11 May 2017 13:29:18 GMT

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

Re: How to extarct first and last timestamp

AMFR — Thu, 11 May 2017 13:36:33 GMT

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