Constraints with the MDY() function

[goat]

Since the beginning of my SAS experience way back in 1996, I've been disappointed with the MDY(month,day,year) function.  As you know, MDY() has the following constraints: 

• month - specifies a numeric                           constant, variable, or expression that represents an integer from                           1 through 12.

• day     - specifies a numeric                           constant, variable, or expression that represents an integer from                           1 through 31.                       

MDY() will return an error if those constraints are not met. 

Let me tell you why I'm disappointed.  I used Excel for several years prior to learning SAS and grew accustomed to its DATE(year,month,day) function.  As you may know, the DATE() function behaves just like MDY(), but without the constraints.  Here is where it gets interesting.  Suppose you wanted to determine the last day of a particular month, say March.  With the date() function, you can simply enter the first day of the following month minus one day, e.g.,

=date(2011,1,3+1)-1

In this case, I compute the first day of April and subtract one day to get the last day of march.  Of course, this logic will work with MDY as well:

mdy(3+1,1,2011) - 1

However, MDY() will error out if I'm at month 12, e.g. mdy(12+1,1,2011) -1

This is not the case with Excel's date function as it can handle any month integer.  Month 13 is simply the following year's month 1, e.g. 1/1/2012 = date(2011,13,1)

There are many useful cases there you might want to cycle through months or days outside of MDY()'s constraints.  So I created my own function called ymd(year,month,day) which removes these constraints.  Here it is if you want to try it:

proc fcmp outlib=work.subs.ymd;

  function ymd(year,month,day) ;

   year_add = floor((month-1)/12);

   monthnum = ((month - 1) - 12*year_add)+1;

   d = mdy(monthnum,1,year+year_add) + (day - 1);

   return(d);

  endsub;

run; quit;

Dave

[Tom]

Why not just use the INTNX function that SAS already has?

[goat]

INTNX requires too many text based arguments.  I think its much easier to generate simple do loops for month, day or both without the need to add logic to apply the text parameters

[Tom]

If you allow MDY to accept 13 as a valid month then you lose the ability to detect when users have accidentally put the DAY value into the MONTH field.

Not sure why the parameters for INTNX are any harder to use than the parameters for MDY or Excel's DATE function.  To use those you have first split your dates into three fields. For example if you have your date value in the variable A3 (or the Excel cell reference A3) here is the comparison to calculate the last day of the current month.

Excel syntax: =DATE(YEAR(A3),MONTH(A3)+1,DAY(A3))-1

SAS syntax: =INTX('month',A3,0,'end')

[goat]

Tom - good point and example.  Thanks for sharing.

[Reeza]

My concern with this is for someone coming behind you and using your code and understanding it...the additional documentation required seems like a lot more text than a few parameters to the intnx function.