BookmarkSubscribeRSS Feed
hwangnyc
Quartz | Level 8

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!

 

 

7 REPLIES 7
Astounding
PROC Star

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?

ballardw
Super User

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;
hwangnyc
Quartz | Level 8

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?

ballardw
Super User

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

Astounding
PROC Star

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.

hwangnyc
Quartz | Level 8

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!

Astounding
PROC Star

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.

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
  • 7 replies
  • 765 views
  • 0 likes
  • 3 in conversation