BookmarkSubscribeRSS Feed
chennupriya
Quartz | Level 8

Hi

I need help in converting this formula to sas

iin excel I have formula

EOMONTH(AD$2,-1)-EOMONTH(AD$2,-2)

that means here AD is 3/31/2015

so the difference I shd use in my formula

ccan anyone help

5 REPLIES 5
Kurt_Bremser
Super User

Had to google eomonth. Remember that this is a SAS forum, not an Excel one.

I guess what you are looking for is the intnx function, which can handle all kinds of date intervals and alignments like end of month.

Call it like

newdate = intnx('month',date,-1,'end');

Your formula would most probably be

result = intnx('month',date,-1,'end') - intnx('month',date,-2,'end');

which will yield the number of days between the last day of the last month and the last day of the next-to-last month.

Patrick
Opal | Level 21

Or as this formula simply determines the number of days of the previous month you could also use:

result=day(intnx('month',date,-1,'end')

Should you need to determine the number of days of the previous month for financial interest calculations (with a 30 day month if it's the last day of the month) then the DATDIF() function could make your life much easier SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition

chennupriya
Quartz | Level 8

hi ,

i have used Datdif function and intik also but I am getting negative -28

i mean dates cant be negative so. I want to avoid negative sign . Can anyone help

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post some test data in a datastep so we can see what your doing.  It is possible to have negative dates:

Difference in days between 10JAN15 - 15JAN15 can be 5 days, or -5 days depending on which way round you put them in the function.

ronan
Lapis Lazuli | Level 10

Some Excel functions are already implemented in SAS using FCMP custom functions :

http://www.sascommunity.org/wiki/Excel_functions_in_SAS

Complete doc for FCMP :  FCMP :: Base SAS(R) 9.4 Procedures Guide, Third Edition

A nice presentation by SAS R&D : http://www.gasug.org/papers/ProcFCMP.pptx

Here is, for instance, the Excel DATDIF function coded into SAS as a SAS function called DATDIF4_SLK :

proc fcmp OUTLIB=SASHELP.SLKWXL.finance;

function datdif4_slk(start, end)

          label="European DATDIF";/*-----------------------------------------------------------------

  * ENTRY:     datdif4_slk

  *

  * PURPOSE:   Returns the number of days between two dates using

  *            the European '30/360' method of calculation.

  *

  * USAGE:     numdays = datdif4_slk( start, end );

  *               start - the start date from which to calculate

  *                       number of days, expressed as SAS date

  *                       value, e.g., '15feb98'd.

  *               end   - the end date from which to calculate

  *                       number of days, expressed as SAS date

  *                       value, e.g., '15mar98'd.

  *

  * NOTES:     If either start date or end date is on the 31st of

  *            a month, then it is set equal to the 30th of the same

  *            month. If the start date is on the last day of a

  *            February, then the difference between 30 and the

  *            length of the same year's February is added to the

  *            usual U.S.(NASD) method of '30/360' day count basis.

  *-----------------------------------------------------------------*/

    startday = day(start);

    startmon = month(start);

    startyear = year(start);

    endday = day(end);

    endmon = month(end);

    endyear = year(end);

    if startday = 31 then

                          startday = 30;

    if endday = 31 then

                        endday = 30;

    restart = mdy(startmon,startday,startyear);

    reend = mdy(endmon,endday,endyear);

    datdif4 = datdif(restart,reend,'30/360');

   /* adjust for february start and falls on the last day of february */

    febstart = mdy(2,1,startyear);

    marstart = mdy(3,1,startyear);

    feblength = datdif(febstart,marstart,'act/act');

    if feblength = 28 & startday = 28 then

        datdif4 = datdif(start,end,'30/360') + 2;

    if feblength = 29 & startday = 29 then

        datdif4 = datdif(start,end,'30/360') + 1;

    return(datdif4);

endsub;

run;

quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2844 views
  • 0 likes
  • 5 in conversation