DATA Step, Macro, Functions and more

How to extarct first and last timestamp

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

How to extarct first and last timestamp

[ Edited ]

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;

 

 


Accepted Solutions
Solution
‎05-11-2017 09:16 AM
Super User
Super User
Posts: 7,392

Re: How to extarct first and last timestamp

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.

View solution in original post


All Replies
Super User
Posts: 6,928

Re: How to extarct first and last timestamp

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: How to extarct first and last timestamp

 

 

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

Super User
Posts: 6,928

Re: How to extarct first and last timestamp

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: How to extarct first and last timestamp

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

Super User
Posts: 6,928

Re: How to extarct first and last timestamp


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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: How to extarct first and last timestamp

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

Solution
‎05-11-2017 09:16 AM
Super User
Super User
Posts: 7,392

Re: How to extarct first and last timestamp

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.

Contributor
Posts: 28

Re: How to extarct first and last timestamp

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 129 views
  • 0 likes
  • 3 in conversation