## Convert excel formula to SAS

Frequent Contributor
Posts: 125

# Convert excel formula to SAS

Hi

I need help in converting this formula to sas

iin excel I have formula

that means here AD is 3/31/2015

so the difference I shd use in my formula

ccan anyone help

Super User
Posts: 10,574

## 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
How to convert datasets to data steps
How to post code
Posts: 4,797

## 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: 125

## 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

Super User
Posts: 9,840

## 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: 148

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

```
Discussion stats
• 5 replies
• 1607 views
• 0 likes
• 5 in conversation