How to assign a numeric value based on a date range

Reply
New Contributor
Posts: 2

How to assign a numeric value based on a date range

I'm still relatively new to SAS and I've been searching the web for an answer but have yet to find one. I want to be able to assign a numeric value (say 1 - 7) to a date range with 1 being the earliest date and 7 the latest. I can code a data step to assign this based of a specific date, e.g. 8/6/2012 = 1, 8/7/2012 = 2, etc., but this is fairly limited in the approach I want to achieve. I want this to be as dynamic as possible with the first date in a given range to be equal to 1 and the last to be equal to N (how ever many days). I know I can do this with a loop, but for what ever reason I simply cannot figure out exactly what needs to be counted.

Any help would be appreciated.

Thank you.

Super User
Super User
Posts: 7,079

Re: How to assign a numeric value based on a date range

Sounds like you want to generate a relative day variable, such as a StudyDay variable for a clinical trial.

So you just need to subtract the baseline date from the existing date value. (Note some add one so that the first day is 1 instead of zero.)

One way is when the first date is to be treated as the baseline date.  You can use RETAIN to carry that value onto the following dates.

data new;

  set old;

  by id ;

  retain baseline;

  if first.id then baseline=date ;

  day = date - baseline +1 ;

format baseline date.;

run;

Another is when the date is defined externally and merged onto the existing data.  This is also useful when you want to allow for negative day values to indicate events that preceed the baseline.  So if you have an already existing dataset with one row per ID value you can merge it onto you existing data and the baseline variable will be populated for each row.

data want ;

  merge have baselines;

  by id;

  day = date - baseline + 1;

run;

New Contributor
Posts: 2

Re: How to assign a numeric value based on a date range

Thanks Tom. I believe this will help a lot.

Additionally, will this process works if you explicitly state start and end dates, e.g. start date = 8/6/2012 and end date = 8/12/2012 with 8/6/2012 = 1 and 8/12/2012 = 7?

Thanks again.

Super User
Super User
Posts: 7,079

Re: How to assign a numeric value based on a date range

You can use date literals in SAS programs. Also what about dates outside the range?

if '06AUG2012'd <= date <= '12AUG2012'd then day = date - '06AUG2012'd + 1;

else day=.;

Ask a Question
Discussion stats
  • 3 replies
  • 389 views
  • 0 likes
  • 2 in conversation