BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AMFR
Quartz | Level 8

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
Kurt_Bremser
Super User

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
Quartz | Level 8

 

 

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
Super User

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
Quartz | Level 8

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
Super User

@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
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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