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;
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 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;
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;
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;
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;
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
Thank you for your help. Its clear now.
Do Fio2 and Spo2 both always exist for one time?
If not: what is the expected output in such case?
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
@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_.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: