topic Re: Set a new variable as the first occurrence in a list in SAS Programming

Set a new variable as the first occurrence in a list

braverju — Sat, 30 Sep 2017 18:14:47 GMT

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.

Re: Set a new variable as the first occurrence in a list

mkeintz — Sat, 30 Sep 2017 18:25:50 GMT

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).

Re: Set a new variable as the first occurrence in a list

Astounding — Sat, 30 Sep 2017 19:33:17 GMT

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;

Re: Set a new variable as the first occurrence in a list

braverju — Sat, 30 Sep 2017 20:37:54 GMT

Great! Thank you. It works. I'll try to figure out the next steps.

Re: Set a new variable as the first occurrence in a list

braverju — Sat, 30 Sep 2017 20:38:35 GMT

Thank you very much! It's a witty solution! It may work – I do know the maximum visits (It's actually 20).