Hi guys,
suppose to have the following table:
data DB;
input ID :$20. Admission :date09. Discharge :date09. Age_class Age_end Index Value Total;
format Admission date9. Discharge date9.;
cards;
0001 01JUL2014 16AUG2014 1 4 1 2.3 11.9
0001 13MAY2018 22JUN2018 3 4 0 1.4 .
0001 23JAN2019 25JAN2019 4 4 0 3.2 .
0002 13MAY2016 22SEP2016 1 5 1 2 7.9
0002 09JUL2023 10JUL2023 2 5 0 0.3 .
0002 12SEP2024 15SEP2024 3 5 0 0.2 .
0003 01JUL2014 18AUG2014 1 3 1 12 17.3
0003 07DEC2023 16DEC2023 2 3 0 0.3 .
0004 12JAN2014 15JAN2014 1 2 1 2 2.1
0004 30MAY2019 13JUL2019 2 2 0 0.1 .
0005 30JUN2019 13OCT2019 5 5 0 4.1 .
;
run;
Is there a way to get the following?
data DB1;
input ID :$20. Admission :date09. Discharge :date09. Age_class Age_end Index Value Total Age_class1 Age_class2 Age_class3 Age_class4 Age_class5;
format Admission date9. Discharge date9.;
cards;
0001 01JUL2014 16AUG2014 1 4 1 2.3 11.9 2.3 5 1.4 3.2 .
0001 13MAY2018 22JUN2018 3 4 0 1.4 . . . . . .
0001 23JAN2019 25JAN2019 4 4 0 3.2 . . . . . .
0002 13MAY2016 22SEP2016 1 5 1 2 7.9 2 0.3 0.2 5 0.4
0002 09JUL2023 10JUL2023 2 5 0 0.3 . . . . . .
0002 12SEP2024 15SEP2024 3 5 0 0.2 . . . . . .
0003 01JUL2014 18AUG2014 1 3 1 12 17.3 12 0.3 5 . .
0003 07DEC2023 16DEC2023 2 3 0 0.3 . . . . . .
0004 12JAN2014 15JAN2014 1 2 1 2 2.1 2 0.1 . . .
0004 30MAY2019 13JUL2019 2 2 0 0.1 . . . . . .
0005 30JUN2019 13OCT2019 5 5 1 4.1 4.1 . . . . 4.1
;
run;
In other words, for each ID in DB there is the Age_class at each admission and an Age_class at exit (Age_end) from the study. Then, there is the "Value" variable that is the time the patient stay in the corresponding Age_class (before exit). There is also a "Total" time in the study. What I need is to transform the dataset DB into a wide format where there are columns corresponding to the age classes filled by the corresponding Value. Note that in this new dataset the row-wise total must be reached and must be equal to "Total" and so: if there is a jump in the age classes (Age_class variable) a value = 5 years must be added because the age-class interval is of max 5 years.
Finally, in the new wide dataset (DB1) rows must be filled only where Index = 1
Can anyone help me please?
Thank you very much in advance
... View more