BookmarkSubscribeRSS Feed
Calcite | Level 5

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


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);



run; quit;


Super User Tom
Super User

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

Calcite | Level 5

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

Super User Tom
Super User

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')

Calcite | Level 5

Tom - good point and example.  Thanks for sharing.

Super User

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.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3 in conversation