Re: Compute length of stay from 17 character date

desireatem

Please I need help with the SAS code below that does not produce expected reuslt:

Consider the hospital Admit label “HOSP_ADMSN_TIME” is a character variable as below and the hospital discharge time “HOSP_DISCH_TIME” is a character variable as below, and compute the length of stay “LOS” using SAS.

HOSP_ADMSN_TIME	Char	17	$17.	$17.	HOSP_ADMSN_TIME
HOSP_DISCH_TIME	Char	17	$17.	$17.	HOSP_DISCH_TIME

Example of the character data as in excel is as below

HOSP_ADMSN_TIME	HOSP_DISCH_TIME
2022-05-12 11:35:00	2022-05-21 15:46:00
2022-05-12 11:35:00	2022-05-21 15:46:00
2022-05-12 11:35:00	2022-05-21 15:46:00
2022-05-12 11:35:00	2022-05-21 15:46:00
2022-05-12 11:35:00	2022-05-21 15:46:00
2022-05-12 11:35:00	2022-05-21 15:46:00

I used the following code to compute length of stay but the result is wrong please help.

data new_count;

set new_count;

/* Convert character HOSP_ADMSN_TIME to numeric datetime */

dt_HOSP_ADMSN_TIME = input(HOSP_ADMSN_TIME, anydtdtm19.);

/* Convert character HOSP_DISCH_TIME to numeric datetime */

dt_HOSP_DISCH_TIME = input(HOSP_DISCH_TIME, anydtdtm19.);

/* Calculate the Length of Stay (LOS) in days */

LOS = (dt_HOSP_DISCH_TIME - dt_HOSP_ADMSN_TIME) / (60*60*24); /* Convert seconds to days */

/* Format the datetime variables for readability */

format dt_HOSP_ADMSN_TIME datetime19. dt_HOSP_DISCH_TIME datetime19.;

/* Drop the original character variables if not needed */

drop HOSP_ADMSN_TIME HOSP_DISCH_TIME;

/* Rename the new datetime variables to the original names */

rename dt_HOSP_ADMSN_TIME = HOSP_ADMSN_TIME

dt_HOSP_DISCH_TIME = HOSP_DISCH_TIME;

run;

ballardw

You should describe the value of LOS you get and why it is "wrong".

Consider what answer you would expect for admit 2022-05-12 23:35:00 (making a value just before midnight) and discharge of 2022-05-13 07:35:00 just a few hours later in the morning the next day. Should that be one day? Or 0.3 days (roughly, 8 hours is 1/3 of a day).

What if they are discharged on the same calendar day? You would never have a value later than 1. So what should appear?

If you are looking for CALENDAR DAY differences then perhaps you want the INTCK function

data example;
   infile datalines dlm=',';
   input HOSP_ADMSN_TIME :anydtdtm32. HOSP_DISCH_TIME : anydtdtm32.;
   format HOSP_ADMSN_TIME  HOSP_DISCH_TIME datetime20.;
   los = intck('dtdays',HOSP_ADMSN_TIME,HOSP_DISCH_TIME);
datalines;	
2022-05-12 11:35:00,2022-05-21 15:46:00
;

which returns 9 for the example. 'dtday' says to use datetime values and return count of day intervals. This will return 0 for any discharge the same day, 1 for any discharge time the day following admission regardless of admission time on that day.

Having example data repeated multiple times doesn't add much and really needs what you expect the result for the examples to be.

desireatem

I want to compute the length of stay as I said above. I have this data in Excel with 17 character dates as below and I want to compute length of stay, this is the data as in excel:

HOSP_ADMSN_TIME	HOSP_DISCH_TIME
2022-05-12 11:35:00	2022-05-21 15:46:00

I modified my code using the example above but it didnt work as well:

data new_count;

set new_count;

/* Convert character HOSP_ADMSN_TIME to numeric datetime */

dt_HOSP_ADMSN_TIME = input(HOSP_ADMSN_TIME, anydtdtm32.);

/* Convert character HOSP_DISCH_TIME to numeric datetime */

dt_HOSP_DISCH_TIME = input(HOSP_DISCH_TIME, anydtdtm32.);

format dt_HOSP_ADMSN_TIME dt_HOSP_DISCH_TIME datetime20.;

/* Calculate the Length of Stay (LOS) in days */

los = intck('dtdays',HOSP_ADMSN_TIME,HOSP_DISCH_TIME);

drop HOSP_ADMSN_TIME HOSP_DISCH_TIME;

/* Rename the new datetime variables to the original names */

rename dt_HOSP_ADMSN_TIME = HOSP_ADMSN_TIME

dt_HOSP_DISCH_TIME = HOSP_DISCH_TIME;

run;

ballardw

What is the expected result for those datetime values?

"Didn't work" does not provide any usable information for making suggestions. You did not provide a result after the first time asked either.

desireatem

This is what we got which is wrong:

Obs	HOSP_ADMSN_TIME	HOSP_DISCH_TIME
1	.	.
2	.	.
3	.	.
4	.	.
5	.	.
6	.	.

ballardw

Last time, what is the expected correct LOS value given that example pair of datetime values?????

desireatem

Just as I said above:

Consider the hospital Admit label “HOSP_ADMSN_TIME” is a character variable as below and the hospital discharge time “HOSP_DISCH_TIME” is a character variable as below, and compute the length of stay “LOS” using SAS.

HOSP_ADMSN_TIME	Char	17	$17.	$17.	HOSP_ADMSN_TIME
HOSP_DISCH_TIME	Char	17	$17.	$17.	HOSP_DISCH_TIME

Example of the character data as in excel is as below

HOSP_ADMSN_TIME	HOSP_DISCH_TIME
2022-05-12 11:35:00	2022-05-21 15:46:00

I used the following code to compute length of stay but the result is wrong please help.

data new_count;

set new_count;

/* Convert character HOSP_ADMSN_TIME to numeric datetime */

dt_HOSP_ADMSN_TIME = input(HOSP_ADMSN_TIME, anydtdtm19.);

/* Convert character HOSP_DISCH_TIME to numeric datetime */

dt_HOSP_DISCH_TIME = input(HOSP_DISCH_TIME, anydtdtm19.);

/* Calculate the Length of Stay (LOS) in days */

LOS = (dt_HOSP_DISCH_TIME - dt_HOSP_ADMSN_TIME) / (60*60*24); /* Convert seconds to days */

/* Format the datetime variables for readability */

format dt_HOSP_ADMSN_TIME datetime19. dt_HOSP_DISCH_TIME datetime19.;

/* Drop the original character variables if not needed */

drop HOSP_ADMSN_TIME HOSP_DISCH_TIME;

/* Rename the new datetime variables to the original names */

rename dt_HOSP_ADMSN_TIME = HOSP_ADMSN_TIME

dt_HOSP_DISCH_TIME = HOSP_DISCH_TIME;

run;

This is the wrong output that I get:

Obs	HOSP_ADMSN_TIME	HOSP_DISCH_TIME	LOS
1	.	.	.
2	.	.	.
3	.	.	.
4	.	.	.
5	.	.	.
6	.	.	.

Can anyone help me with the correct.

ballardw

You have not shown that you actually generating datetime values at all.

Stop dropping and renaming and show.

Wrong is not any value as to what is the expected result.

I did an example above that 1) successfully creates datetime values and shows one possible result for LOS that you have never stated whether my result was "wrong" or why it did not meet expectations. We cannot help you if you will not actually answer questions.

You have not shared you actually starting data. There multiple reasons that your initial attempt to create a datetime value may fail. Pictures and values copied from Excel have no real value in testing SAS Code. You have character values. You are reading them with an informat that does have to guess as to the content. IF the values actually have leading spaces then the length you attempted to use would fail.

You have Code that starts this way:

data new_count;

    set new_count;

That means you completely replaced the data set. It also means that you have very likely corrupted your initial values of the character version as you renamed and dropped stuff. So you have to go back to the step where you read the data into SAS.

Until you have much more experience in SAS any attempt at recoding existing variables into a same named variable should never use the same data set name on the Data and Set statements because all sorts of minor logic errors will corrupt your data. And rerunning the same code means the corruption is likely to get worse. Send the output to a new data set so you can compare the start and end results.

If you will not answer this question: What is the desired value for LOS when starting with HOSP_ADMSN_TIME 2022-05-12 11:35:00 and HOSP_DISCH_TIME of 2022-05-21 15:46:00, and what UNITS (days, hours, minutes, seconds, fortnights, weeks, months) are the answer then I will have to say that you really don't want the answer.

Also run this code:

Data example;
  x1= "2022-05-12 11:35:00";
  d1 = input(x1, anydtdtm15.);
  d2 = input(x1, anydtdtm16.);
  d3 = input(x1, anydtdtm17.);
  d4 = input(x1, anydtdtm18.);
  d5 = input(x1, anydtdtm19.);

  format d: datetime20.;
run;

Note that the result for using a width of 17 results in missing datetime values because your data does not show 17 characters.

This value

2022-05-12 11:35:00

count them, has 19 characters. 17 is the : between the minutes and seconds and is an incomplete time so the input with the Anydtdtm17 informat returns missing values. So of course any manipulation returns a "wrong" result.

At this point 1) rerun what ever you did to bring the data into SAS. If you are getting a "17" character field that show use the actual SAS values. Not a picture from Excel. The SAS values. If there is supposed to be a minutes component that is not appearing then you need to fix how you read the data. Hint: very likely Proc Import is not your friend if this what you got in the past. Save the file to CSV if it is starting in EXCEL and read that file. Then show us the results.

Tom

There is a disconnect in your description of what you have.

You say the SAS variables are character with a LENGTH of 17 (note that the FORMAT and INFORMAT attached to the variables make no difference in this case since they do not impact what is in the variable).

Then you show values that at are longer then 17 bytes.

To see what you have in the SAS dataset print a few values (without any formats). If you don't know how to see that information you can just run a simple data step and write the values to the SAS log.

So if you have the data in a dataset named HAVE this data step will write the values from the first 5 observations to the SAS log.

data _null_;
  set HAVE (obs=5);
  put (_n_ HOSP_ADMSN_TIME HOSP_DISCH_TIME) (=);
  format HOSP_ADMSN_TIME HOSP_DISCH_TIME ;
run;

Then copy and paste the text from the SAS log into the pop-up window you get when you click on the Insert Code icon.

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!