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

[TheBlackBishop1]

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;

[PaigeMiller]

---

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

[TheBlackBishop1]

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

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;

[HarrySnart · Accepted solution]

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;

[TheBlackBishop1]

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;

[Tom]

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

[TheBlackBishop1]

Thank you for your help. Its clear now.

[andreas_lds]

Do Fio2 and Spo2 both always exist for one time?

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

[TheBlackBishop1]

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

[HarrySnart]

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

[ballardw]

---

@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_.