topic Re: How do I seperate a variable by datetime to capture values at a specific time only in SAS Programming

How do I seperate a variable by datetime to capture values at a specific time only

TheBlackBishop1 — Fri, 22 Apr 2022 09:58:38 GMT

I want to pick values of Spo2 and Fio2 that occur at the same time. I am trying to transform the have to want dataset. I have a huge dataset but i have created a subset here for clarity. Please help i am a new programmer.

data have;
input datetime $disp_name Value;
datalines;
1/1/2012 1:29:00 PM SpO2 90
1/1/2012 1:29:00 PM Fio2 100
1/1/2012 2:21:00 PM SpO2 89
1/1/2012 2:21:00 PM Fio2 100
1/1/2012 4:09:00 PM SpO2 87
1/1/2012 4:09:00 PM Fio2 99
;
run;

data want;
input datetime SpO2 Fio2;
datalines;
1/1/2012 1:29:00 PM 90 100
1/1/2012 2:21:00 PM 89 100
1/1/2012 4:09:00 PM 87 99
;
run;

Re: How do I seperate a variable by datetime to capture values at a specific time only

PaigeMiller — Fri, 22 Apr 2022 10:48:47 GMT

@TheBlackBishop1 wrote:

data have;
input datetime $disp_name Value;
datalines;
1/1/2012 1:29:00 PM SpO2 90
1/1/2012 1:29:00 PM Fio2 100
1/1/2012 2:21:00 PM SpO2 89
1/1/2012 2:21:00 PM Fio2 100
1/1/2012 4:09:00 PM SpO2 87
1/1/2012 4:09:00 PM Fio2 99
;
run;

Hello @TheBlackBishop1 I realize you are new here, but it would help everyone if you would please test your code before posting to make sure it works. This code doesn't work.

Here is my solution, which is untested, because I don't have your data (or working code from you)

proc transpose data=have out=want;
    by datetime;
    var value;
    id disp_name;
run;

Re: How do I seperate a variable by datetime to capture values at a specific time only

HarrySnart — Fri, 22 Apr 2022 10:49:21 GMT

Hi @TheBlackBishop1 your input datalines didn't work but this can be achieved using PROC TRANSPOSE.

proc print data=have;title 'Initial Data';run;

proc sort data=WORK.HAVE out=WORK.SORTTempTableSorted;
	by datetime;
run;

proc transpose data=WORK.SORTTempTableSorted out=want ;
	var value;
	id disp_name;


	by datetime;
run;

proc print data=want;
	title "Output of Transpose Procedure"; var datetime sp02 fio2;
run;

Re: How do I seperate a variable by datetime to capture values at a specific time only

andreas_lds — Fri, 22 Apr 2022 11:39:56 GMT

Do Fio2 and Spo2 both always exist for one time?

If not: what is the expected output in such case?

Re: How do I seperate a variable by datetime to capture values at a specific time only

TheBlackBishop1 — Fri, 22 Apr 2022 13:50:24 GMT

FiO2 and Spo2 don’t always exist for one time. I am only interested in when
they both exist for one time.

Re: How do I seperate a variable by datetime to capture values at a specific time only

HarrySnart — Fri, 22 Apr 2022 13:55:39 GMT

In that case you could still use PROC TRANSPOSE and for the output run a datastep with a where clause removing null instance of FiO2 and Spo2

Re: How do I seperate a variable by datetime to capture values at a specific time only

TheBlackBishop1 — Fri, 22 Apr 2022 14:33:22 GMT

Thank you for this response. But i have one challenge. I need to maintain them by both the mrn and enc (encounter id). How would the proc transpose work in this case? (sorry about my input code. I have corrected it and it works now)

data have;
input mrn enc datetime$ 9-26 disp_name$ Value;
datalines;
123 341 1/1/2012 1:29:00PM SpO2 90
123 341 1/1/2012 1:29:00PM Fio2 100
123 341 1/1/2012 2:21:00PM SpO2 89
123 341 1/1/2012 2:21:00PM Fio2 100
245 456 1/1/2012 4:09:00PM SpO2 87
245 456 1/1/2012 4:09:00PM Fio2 99
245 222 1/3/2012 3:09:00PM SpO2 88
245 222 1/3/2012 3:09:00PM Fio2 100
;
run;

Re: How do I seperate a variable by datetime to capture values at a specific time only

TheBlackBishop1 — Fri, 22 Apr 2022 14:38:54 GMT

Hi, I am sorry about my code. This works now. I want to keep them by MRN(medical record number) and ENC (encounter ID). How would the proc transpose work? Thanks

Re: How do I seperate a variable by datetime to capture values at a specific time only

Tom — Fri, 22 Apr 2022 14:48:01 GMT

Just add them to the BY statement.

Not sure why you posted data that is not actually sorted by the variables (at least by the variables in the order they appear in your dataset). You might need to sort the data. Or change the order of the variables in BY statement to match the sort order if it is sorted.

But your sample data is grouped already even if it is not actually sorted so you could add the NOTSORTED keyword to the BY statement.

data have;
input mrn enc datetime$ 9-26 disp_name$ Value;
datalines;
123 341 1/1/2012 1:29:00PM SpO2 90
123 341 1/1/2012 1:29:00PM Fio2 100
123 341 1/1/2012 2:21:00PM SpO2 89
123 341 1/1/2012 2:21:00PM Fio2 100
245 456 1/1/2012 4:09:00PM SpO2 87
245 456 1/1/2012 4:09:00PM Fio2 99
245 222 1/3/2012 3:09:00PM SpO2 88
245 222 1/3/2012 3:09:00PM Fio2 100
;

proc transpose data=have out=want(drop=_name_);
  by mrn enc datetime NOTSORTED ;
  var value;
  id disp_name;
run;

proc print;
run;

Obs    mrn    enc         datetime         SpO2    Fio2

 1     123    341    1/1/2012 1:29:00PM     90      100
 2     123    341    1/1/2012 2:21:00PM     89      100
 3     245    456    1/1/2012 4:09:00PM     87       99
 4     245    222    1/3/2012 3:09:00PM     88      100

Re: How do I seperate a variable by datetime to capture values at a specific time only

TheBlackBishop1 — Fri, 22 Apr 2022 15:05:23 GMT

Thank you for your help. Its clear now.

Re: How do I seperate a variable by datetime to capture values at a specific time only

ballardw — Fri, 22 Apr 2022 17:08:21 GMT

@HarrySnart wrote:

In that case you could still use PROC TRANSPOSE and for the output run a datastep with a where clause removing null instance of FiO2 and Spo2

Or a data set option on the output data set of proc transpose with something like where=( not missing(FiO2) and not missing(Spo2)) along with the drop _name_.