Desktop productivity for business analysts and programmers

Convert excel formula to SAS

Reply
Frequent Contributor
Posts: 120

Convert excel formula to SAS

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

Esteemed Advisor
Posts: 6,661

Re: Convert excel formula to SAS

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,834

Re: Convert excel formula to SAS

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

Frequent Contributor
Posts: 120

Re: Convert excel formula to SAS

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: Convert excel formula to SAS

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.

Frequent Contributor
Posts: 117

Re: Convert excel formula to SAS

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;

Ask a Question
Discussion stats
  • 5 replies
  • 1059 views
  • 0 likes
  • 5 in conversation