Dates

[Ela_84]

Hi everyone,

I have a field of due days listed as below : How would i calculate in sas eg if due day in (30,31) then last paid date = 29FEB2020. 

this is the code i wrote :

Data Test;
Set Test_1;
Format lastPaiDdate date9.;

if DueDay >= today then lastPaiDdate = (MDY(month-1,DueDay,year));
else if DueDay < today then lastPaiDdate = (MDY(month,DueDay,year));
run;

Table I want to create below :

DueDay	LastDatePaid
1	01-Feb-20
2	02-Feb-20
3	03-Feb-20
29	29-Feb-20
30	29-Feb-20
31	29-Feb-20

[PaigeMiller]

Are we assuming that we only need to write code for February 2020, or do we need the code to be more general to handle other months and non-leap years?

[Ela_84]

more general to handle other month and non leap years please.

[PaigeMiller]

So, in the program, we'd have to make use a variable that contains the month and year. How would the program know what month and year to do the calculations for? Your example shows only one input variable, and one output variable, neither of which can be used to determine what month and year the calculations should be done for.

[ballardw]

The better function to use for incrementing dates is INTNX.

You can specify the interval, such as month and indicate whether you want the start, end or same day of the month. And the function takes care of such nasty things as leap years.

If you want the end of the previous month for example then the code would look like:

Lastmonth = intnx('month',date, -1,'E');

The interval is the first parameter, the second is the date to start from,your current value, then is the number of intervals to apply, in this case negative 1 to get the last month. The final parameter 'E' says to get the last day of the interval. 

You don't show a value for Today, and the function Today() would almost certainly never be less than DueDay so I can't be sure what you are comparing.

Here is an example of using INTNX with the 'S' alignment, for Same, applied to a date getting the same "day" in the previous month.

data example;
	do date = '25Mar2020'd to '31Mar2020'd;
		prevdate = intnx('month',date,-1,'S');
		output;
	end;
	format date prevdate date9.;
run;

Note that all of 29, 30 and 31 March get mapped to 29 Feb.

If you change the year in the DO loop to a non-leap year then you would see all of 28 to 31 March mapped to 28 Feb.

[Ela_84]

thank you for your reply, I will try to apply this logic to my code and will let you know it goes.

thank you again

[yabwon · Accepted solution]

Hi,

maybe something like this:

data have;
input DueDay;
cards;
1
2
3
29
30
31
;
run;

data want;
  month=2;
  year=2020;

  max_day = day(intnx("month", mdy(month, 1, year),0,"end"));

  set have;

  DueDate = mdy(month, max_day><DueDay, year);
  format DueDate yymmdd10.;
  keep Due:;
run;

All the best

Bart

[Ela_84]

thanks Bart, I will try this logic and let you know how it goes.

appreciate the response

[Tom]

Use INTNX() to find the last day of the month.

Example:

data test;
  do year=2020;
    do month=1 to 2 ;
      do dueday=27 to 31 ;
         date=input(catx('-',year,month,dueday),??yymmdd10.);
         if missing(date) then date=intnx('month',mdy(month,1,year),0,'e');
         output;
      end;
    end;
  end;
  format date yymmdd10.;
run;
proc print ;run;

Results:

Obs    year    month    dueday          date

  1    2020      1        27      2020-01-27
  2    2020      1        28      2020-01-28
  3    2020      1        29      2020-01-29
  4    2020      1        30      2020-01-30
  5    2020      1        31      2020-01-31
  6    2020      2        27      2020-02-27
  7    2020      2        28      2020-02-28
  8    2020      2        29      2020-02-29
  9    2020      2        30      2020-02-29
 10    2020      2        31      2020-02-29

[Tom]

You can do it one statement if you want. Use JANUARY of the same year to make a date that can use any valid dueday. Then use INTNX to offset by the number of months from JANUARY to your target month using the SAME option.

date=intnx('month',mdy(1,dueday,year),intck('month',mdy(1,1,year),mdy(month,1,year)),'same');