SAS Enterprise Guide

Desktop productivity for business analysts and programmers
BookmarkSubscribeRSS Feed
goat
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.,

=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

5 REPLIES 5
Tom
Super User Tom
Super User

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

goat
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

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

goat
Calcite | Level 5

Tom - good point and example.  Thanks for sharing.

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

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 11370 views
  • 0 likes
  • 3 in conversation