DATA Step, Macro, Functions and more

Set a new variable as the first occurrence in a list

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

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?

 

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. 


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

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;

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,288

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,541

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

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

Posted in reply to Astounding
Great! Thank you. It works. I'll try to figure out the next steps.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 199 views
  • 1 like
  • 3 in conversation