I would like to transpose the following dataset so that I can calculate the time difference between the patients first and second hospitalization. How would I do that with duplicate identifiers?
This is how my dataset is presently formatted (and some patients may have more than two hospitalizations)
Patient Name | Patient ID | Admission date | Test date |
Pete Smith | 123 | 1/2/2020 | 12/25/2019 |
Pete Smith | 123 | 3/5/2020 | 12/25/2019 |
Sarah Jones | 456 | 2/2/2020 | 1/25/2020 |
Sarah Jones | 456 | 4/5/2020 | 1/25/2020 |
Mark Adams | 789 | 4/7/2020 | 3/25/2020 |
Mark Adams | 789 | 6/1/2020 | 3/25/2020 |
This is how I would like it to look
Patient Name | Patient ID | Test date | Admission date | Admission date |
Pete Smith | 123 | 12/25/2019 | 1/2/2020 | 3/5/2020 |
Sarah Jones | 456 | 1/25/2020 | 2/2/2020 | 4/5/2020 |
Mark Adams | 789 | 3/25/2020 | 4/7/2020 | 6/1/2020 |
Is transposing the best way to do this? Or would I have to merge?
That data poses no problem for PROC TRANSPOSE.
proc transpose data=have out=want(drop=_name_) prefix=admitdate ;
by patientid patientname testdate;
var admitdate;
run;
Since you cannot have two variables with the same name, this is impossible.
Alternatively, without changing data structure, it is possible to calculate time difference (eg. how many days) using proc sql summary functions.
Eg:
data have;
infile cards dlm=',' truncover;
length PatientName $20;
input PatientName $ PatientID $ Admissiondate:mmddyy10. Testdate: mmddyy10.;
format Admissiondate Testdate date11.;
cards;
Pete Smith, 123, 1/2/2020, 12/25/2019
Pete Smith, 123, 3/5/2020, 12/25/2019
Sarah Jones, 456, 2/2/2020, 1/25/2020
Sarah Jones, 456, 4/5/2020, 1/25/2020
Mark Adams, 789, 4/7/2020, 3/25/2020
Mark Adams, 789, 6/1/2020, 3/25/2020
;
proc sql;
create table want as
select*, min(Admissiondate) as FirstAdmissionDate, max(Admissiondate) as LastAdmissionDate
from have
group by 1, 2, 4
order by 2,3;
quit;
Days= LastAdmissionDate - FirstAdmissionDate;
@cc15 wrote:
... so that I can calculate the time difference between the patients first and second hospitalization ...
No need to transpose to do that.
data want;
set have;
by patientid admitdate ;
days = dif(admitdate);
if first.patientid then do;
admitdate1 = admitdate;
retain admitdate1 ;
format admitdate1 yymmdd10.;
days=0;
end;
if last.patientid;
run;
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: