BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
braverju
Obsidian | Level 7

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
braverju
Obsidian | Level 7
Thank you very much! It's a witty solution! It may work – I do know the maximum visits (It's actually 20).
Astounding
PROC Star

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;

 

braverju
Obsidian | Level 7
Great! Thank you. It works. I'll try to figure out the next steps.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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