date calculation

[Smitha9]

dataset:

ID       Date

a_1 03/02/2003

a_2 04/03/2004

b_1 05/06/2004

b_2 06/07/2009

I want the dataset:

ID             Date                Date I want

a_1       03/02/2003       04/02/2004

a_2       04/03/2004       05/05/2004

b_1       05/06/2004       06/06/2004

b_2       06/07/2009      

Can I do this in SAS

[SASKiwi]

Please advise what rule you are applying to your dates. How does it differ from your previous post about adding one day to your dates? 

[Kurt_Bremser]

Do a look-ahead, as described here 

[ballardw]

---

@Smitha9 wrote:

dataset:

ID       Date

a_1 03/02/2003

a_2 04/03/2004

b_1 05/06/2004

b_2 06/07/2009

I want the dataset:

ID             Date                Date I want

a_1       03/02/2003       04/02/2004

a_2       04/03/2004       05/05/2004

b_1       05/06/2004       06/06/2004

b_2       06/07/2009      

Can I do this in SAS

---

Adjusting a date to the same day of the month next month, barring odd requirements for differing lengths of months is easy. You better make sure that we know which of the values is the month and which the day of the month because it is not obvious.

If you have a date value then you can get the same of the month in the following month using:

Newdate = intnx('month', date,1,'s');

The 'month' is the interval to adjust, one , 1, says adjust for 1 month and the 's' means "same day". Ends of months with 31 days will get mapped to 30 (or 28/29 if the next month is February).

If you data is not in mmddyy format then more explicit rules would be needed to even get close

However, what is the rule for 04/03/2004 becoming 05/05/2004? Or are you implying that we need to look ahead to the next record and set a value? You really need to specify a rule. If the ID plays any role whatever in assigning values then that use, and how, is critical.

[mkeintz · Accepted solution]

---

@Smitha9 wrote:

dataset:

ID       Date

a_1 03/02/2003

a_2 04/03/2004

b_1 05/06/2004

b_2 06/07/2009

I want the dataset:

ID             Date                Date I want

a_1       03/02/2003       04/02/2004

a_2       04/03/2004       05/05/2004

b_1       05/06/2004       06/06/2004

b_2       06/07/2009      

Can I do this in SAS

---

Yes, quite easily in a DATA step:

In words (which would be a much better topic identity the "date calculation", you apparent want to form date ranges (from current date to just-before the next date):

Something like 

data have;
  input id :$3. date mmddyy10.;
  format date date9.;
datalines;
a_1 03/02/2003
a_2 04/03/2004
b_1 05/06/2004
b_2 06/07/2009
run;
data want (drop=_:);
   merge have
              have (firstobs=2 keep=date rename=(date=_nxt_date))
  end=end_of_have;
  if end_of_have=0 then  through_date=_nxt_date-1;
  format through_date date9. ;
run;

data _null_; set _last_; put (_all_) (=); run;

The "trick" here is the self merge that merges the second obs (the "firstobs=2" with date renamed to _nxt_date) with the first obs (having the initial date).  Then the through_date is just 1 less than _nxt_date, which is calculated for ever instance except the last (because there will be no _nxt_date).