Solved
Contributor
Posts: 32

# Set a new variable as the first occurrence in a list

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?

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.

Accepted Solutions
Solution
‎09-30-2017 04:37 PM
Super User
Posts: 6,934

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

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;

All Replies
Posts: 1,394

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

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

Contributor
Posts: 32

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

Thank you very much! It's a witty solution! It may work – I do know the maximum visits (It's actually 20).
Solution
‎09-30-2017 04:37 PM
Super User
Posts: 6,934

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

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;

Contributor
Posts: 32