topic Re: Creating a variable from different dates in SAS Programming

Creating a variable from different dates

hwangnyc — Wed, 13 Apr 2016 18:16:09 GMT

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!

Re: Creating a variable from different dates

Astounding — Wed, 13 Apr 2016 19:18:10 GMT

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?

Re: Creating a variable from different dates

ballardw — Wed, 13 Apr 2016 21:50:53 GMT

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;

Re: Creating a variable from different dates

hwangnyc — Wed, 13 Apr 2016 22:34:40 GMT

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?

Re: Creating a variable from different dates

ballardw — Wed, 13 Apr 2016 23:22:28 GMT

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

Re: Creating a variable from different dates

Astounding — Thu, 14 Apr 2016 02:43:41 GMT

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.

Re: Creating a variable from different dates

hwangnyc — Thu, 14 Apr 2016 13:38:35 GMT

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!

Re: Creating a variable from different dates

Astounding — Thu, 14 Apr 2016 16:05:57 GMT

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.