topic Re: Convert excel formula to SAS in SAS Enterprise Guide

Convert excel formula to SAS

chennupriya — Thu, 16 Apr 2015 21:09:28 GMT

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

Re: Convert excel formula to SAS

Kurt_Bremser — Fri, 17 Apr 2015 06:30:17 GMT

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.

Re: Convert excel formula to SAS

Patrick — Fri, 17 Apr 2015 07:48:29 GMT

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

Re: Convert excel formula to SAS

ronan — Fri, 17 Apr 2015 08:47:05 GMT

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;

Re: Convert excel formula to SAS

chennupriya — Wed, 22 Apr 2015 13:47:28 GMT

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

Re: Convert excel formula to SAS

RW9 — Wed, 22 Apr 2015 13:57:50 GMT

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.