Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Convert excel formula to SAS

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-16-2015 05:09 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chennupriya

04-17-2015 02:30 AM

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

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chennupriya

04-17-2015 03:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

04-22-2015 09:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chennupriya

04-22-2015 09:57 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chennupriya

04-17-2015 04:47 AM

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;