topic Re: date calculation in New SAS User

date calculation

Smitha9 — Wed, 09 Dec 2020 22:49:21 GMT

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

Re: date calculation

SASKiwi — Wed, 09 Dec 2020 23:01:43 GMT

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?

Re: date calculation

Kurt_Bremser — Wed, 09 Dec 2020 23:02:48 GMT

Do a look-ahead, as described here

Re: date calculation

ballardw — Wed, 09 Dec 2020 23:04:11 GMT

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

Re: date calculation

mkeintz — Wed, 09 Dec 2020 23:05:54 GMT

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