Creating a variable from different dates

Reply
Contributor
Posts: 53

Creating a variable from different dates

Hi everyone,

 

I need to create a new variable called “Appointment” which will consist 5 categories – Baseline, 3 Month, 6 Month, 9 Month, and 12 Month.

Some sample data looks like  looks like this:

 

ID EventDate
2 17NOV2008
2 23SEP2009
2 17NOV2008
2 23SEP2009
2 17NOV2008
2 17NOV2008
3 22DEC2008
3 24JAN2008
3 19FEB2008
3 24JAN2008
4 24JAN2008
4 10APR2008
4 08JUL2008
4 22DEC2008
4 01JUL2009
4 10MAR2010
4 10DEC2010
4 24JAN2008
4 10APR2008
4 22DEC2008
4 24JAN2008
5 27FEB2008
5 18MAR2008
5 03SEP2008
5 22DEC2008
5 16JUN2009
5 22MAR2010
5 27FEB2008
5 18MAR2008
5 27FEB2008

What I need is: 

Baseline = Earliest “EventDate” per “ID”

3 Month = “EventDate” between 31 and 59 days  

6 Month = “EventDate” between 60 and 179 days

9 Month = “EventDate” between 180 and 269 days

12 Month = “EventDate” between 270 and 365 days

My challenge is that everyone has a different baseline date. Any suggestions as to how to work around this?

 

Thanks in advance!

 

 

Super User
Posts: 5,368

Re: Creating a variable from different dates

Are your dates character strings, or are they already SAS dates?

 

How would you like to handle it if a patient has 5 visits that all fall within the 6-month window?

Super User
Posts: 11,134

Re: Creating a variable from different dates

What about the multiples of the same date? For instance Id 2 has 4 eventdates of 17NOV2008.

 

I thought this might be a start point but the duplications may not work.

data have;
   informat id $5. Eventdate date9.;
   Format EventDate date9.;
   input ID EventDate;
datalines;
2 17NOV2008
2 23SEP2009
2 17NOV2008
2 23SEP2009
2 17NOV2008
2 17NOV2008
3 22DEC2008
3 24JAN2008
3 19FEB2008
3 24JAN2008
4 24JAN2008
4 10APR2008
4 08JUL2008
4 22DEC2008
4 01JUL2009
4 10MAR2010
4 10DEC2010
4 24JAN2008
4 10APR2008
4 22DEC2008
4 24JAN2008
5 27FEB2008
5 18MAR2008
5 03SEP2008
5 22DEC2008
5 16JUN2009
5 22MAR2010
5 27FEB2008
5 18MAR2008
5 27FEB2008
run;

/* next bit assumes data is sorted by id eventdate*/
proc sort data = have; by id eventdate;run;

data want;
   set have;
   by id eventdate;
   retain basedate;
   if first.id then basedate=eventdate;
   DaysSinceBase = EventDate - BaseDate;
run;
Contributor
Posts: 53

Re: Creating a variable from different dates

Hi folks,

 

Thanks for the response. The date is in SAS DATE9. Format. I left out a column of data that indicates an action complete. Therefore, it's okay to have mutiple IDs and dates because they can be filtered by the variable I left out. 

 

I was thinking, It may be easier to create a Baseline/Last Follow up variable first. So the variable would be 

 

Earliest date = Baseline

Latest date = Last Follow up 

 

Perhaps a method using the First. Last. method?

Super User
Posts: 11,134

Re: Creating a variable from different dates


hwangnyc wrote:

Hi folks,

 

Thanks for the response. The date is in SAS DATE9. Format. I left out a column of data that indicates an action complete. Therefore, it's okay to have mutiple IDs and dates because they can be filtered by the variable I left out. 

 

I was thinking, It may be easier to create a Baseline/Last Follow up variable first. So the variable would be 

 

Earliest date = Baseline

Latest date = Last Follow up 

 

Perhaps a method using the First. Last. method?


Note that the example code uses First but only on ID. For first. logic to work you need something else with each record to idendify the group. Possibly that would be in the other variables you didn't include (and likely should now to see if this approach is feasible.)

Super User
Posts: 5,368

Re: Creating a variable from different dates

Given that EventDate is a SAS date, you ought to be able to code along these lines:

 

proc sort data=have;

by id EventDate;

run;

data want;

set have;

by id EventDate;

if first.id then baseline_date = EventDate;

retain baseline_date;

days_since = EventDate - baseline_date;

**** Use Days_Since to create the variable you want;

run;

 

It should be straightforward to create your new variable based on Days_Since at that point.

Contributor
Posts: 53

Re: Creating a variable from different dates

Astounding - your code worked like a chart. I'm having some difficulties createing the final date. I used the last, statement below:

 

data Events2;
set Events;
by childid AsthmaEventDate;
if first.childid then BaselineEventDate = AsthmaEventDate;
if last.childid then FinalEventDate = AsthmaEventDate;
retain
BaselineEventDate
FinalEventDate;
format
BaselineEventDate date9.
FinalEventDate date9.
;
run;

 

The final event date variable ends up wrong for example 1 case has the FinalEventDate as 22APR2008 when it's really 30JUL2015. Could this be a sorting issue?

 

Thanks!

Super User
Posts: 5,368

Re: Creating a variable from different dates

I don't see either of those dates in your sample data.  We'll have to agree on which data we're using here if we are to compare results.

Ask a Question
Discussion stats
  • 7 replies
  • 418 views
  • 0 likes
  • 3 in conversation