Dear SAS users,
I have the following "vertical" dataset that represents doctor visits for 4 patients (every patient may have one or more visits):
PatientID visitN visitDate labResult
1 0 03/15/2016 10
1 1 03/15/2017 20
1 2 09/20/2017 20
2 0 06/15/2016 30
2 1 10/15/2016 30
3 0 03/15/2016 10
4 0 05/15/2016 20
4 1 08/20/2016 20
4 2 03/15/2017 20
I would like to create several new variables:
1. T1 = to the FIRST visit (i.e., visitN = 0) . Basically, my T1 for each patient will be equal to bolded dates.
2. nDaysSince1stvisit - number of days since T1 (i.e., since the first visit)
May somebody direct me to how to do it?
Thank you very much ahead.
Julia
P.S. Eventually, I would like to get a "horizontal" dataset with 1 line per patient and with the labResults data for T1 (first visit), T2_1month (the visit in 1 month since the first one), and T3_1year (the visit in 1 year since the first one). However, to avoid confusion, I thought I would rather ask step by step.
I'll go with the step-by-step approach, since you may find there are difficult decisions to make about which visit is the one to use for the 1 month or 1 year date. So to answer your original questions, I have to assume that visitDate is a valid SAS date (not a text string), and the data is in order as indicated. Then:
data want;
set have;
by patientID VisitN;
days_since + dif(visitDate);
if first.patientID then days_since = 0;
run;
If you know the maximum number of visits to expect (say 5 in the example below), then you can do a merge of five data sets, where each of those datasets is a subset based on a visitn value. You have to rename vars from each of the subsets:
data have;
input PatientID visitN visitDate :mmddyy10. labResult;
format visitdate date9.;
datalines;
1 0 03/15/2016 10
1 1 03/15/2017 20
1 2 09/20/2017 20
2 0 06/15/2016 30
2 1 10/15/2016 30
3 0 03/15/2016 10
4 0 05/15/2016 20
4 1 08/20/2016 20
4 2 03/15/2017 20
run;
data want;
merge
have (where=(visitn=0) rename=(visitdate=date1 labresult=lab1))
have (where=(visitn=1) rename=(visitdate=date2 labresult=lab2))
have (where=(visitn=2) rename=(visitdate=date3 labresult=lab3))
have (where=(visitn=3) rename=(visitdate=date4 labresult=lab4))
have (where=(visitn=4) rename=(visitdate=date5 labresult=lab5)) ;
nvisits=visitn+1;
drop visitn;
by patientid;
run;
DATE1 will be the data of the first visit. NVISITS will be the number of visit records. Note the dataset must be sorted by patientid (though not necessarily by visitn within patientid).
I'll go with the step-by-step approach, since you may find there are difficult decisions to make about which visit is the one to use for the 1 month or 1 year date. So to answer your original questions, I have to assume that visitDate is a valid SAS date (not a text string), and the data is in order as indicated. Then:
data want;
set have;
by patientID VisitN;
days_since + dif(visitDate);
if first.patientID then days_since = 0;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.