topic Re: creating before and after dates using other dates variables in SAS Programming

creating before and after dates using other dates variables

AmirSari — Mon, 06 Mar 2023 01:14:29 GMT

Hi everyone,

I am trying to create a before and after specific date based on month and days on two/four other dates depending on a frequency variable. Here is an example of my date:

transac_date freq first_date second_date third_date fourth_date
20150102 2 20190430 20191030 .
20100302 2 20190430 20191030 .
20121102 2 20190430 20191030 .
20020716 4 20250228 20250531 20250831
20021211 4 20250228 20250531 20250831
20030108 4 20250228 20250531 20250831

What I need looks like this:

transac_date freq first_date second_date third_date fourth_date before_tran_date after_trans_date
20150102 2 20190430 20191030 . . 20141030 20150430
20100302 2 20190430 20191030 . . 20091030 20100430
20121102 2 20190430 20191030 . . 20121030 20130430
20020716 4 20250228 20250531 20250831 20251130 20020531 20020831
20121211 4 20250228 20250531 20250831 20251130 20121130 20130228
20030108 4 20250228 20250531 20250831 20251130 20021130 20030228

Any help is greatly appreciated!

Re: creating before and after dates using other dates variables

Kurt_Bremser — Mon, 06 Mar 2023 05:58:02 GMT

Please post the rule for calculating these new variables.

Re: creating before and after dates using other dates variables

ballardw — Mon, 06 Mar 2023 16:21:02 GMT

And example data in the form of a working data step.

We see way too many people post numbers like 20190430 claiming they are "dates". But aren't.

Re: creating before and after dates using other dates variables

AmirSari — Mon, 06 Mar 2023 07:04:49 GMT

I can’t update my original post but what I am trying to do is to create the interest payment dates before and after the transaction date, i.e. transac_date based on the interest payment dates for a random year and given the frequency of the interest payments. For example, in the first row of the sample data, freq=2 so interest is paid twice a year on the same day of the month each year. I have the payment dates for 2019. The first payment occurs on April 30, and the second one on October 30. the transaction date is January 2, 2015, so the payment before this transaction date occurs on October 30, 2014, and the second payment occurs on April 30, 2015. The frequency of the interest payments can be 2, 4, or 12.

Here is the example data set with dates.

data have;
input transac_date :yymmdd10. freq first_date :yymmdd10. second_date :yymmdd10. third_date :yymmdd10. fourth_date :yymmdd10.;
format transac_date first_date second_date third_date fourth_date:yymmdd10.;
cards;
20150102 2 20190430 20191030 . .
20100302 2 20190430 20191030 . .
20121102 2 20190430 20191030 . .
20020716 4 20250228 20250531 20250831 20251130
20021211 4 20250228 20250531 20250831 20251130
20030108 4 20250228 20250531 20250831 20251130
;
run;

Re: creating before and after dates using other dates variables

Kurt_Bremser — Mon, 06 Mar 2023 10:42:36 GMT

See this:

data want;
set have;
select (freq);
  when (2) cadence = 6;
  when (4) cadence = 3;
  when (12) cadence = 1;
end; 
format before_tran_date after_tran_date yymmdd10.;
before_tran_date = first_date;
do while (before_tran_date ge transac_date);
  before_tran_date = intnx('month',before_tran_date,-cadence,'e');
end;
after_tran_date = intnx('month',before_tran_date,cadence,'e');
drop cadence;
run;

Re: creating before and after dates using other dates variables

AmirSari — Mon, 06 Mar 2023 16:24:25 GMT

Thank you! This works like a charm.