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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Intck function (is it possible to not round number...

- 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

01-26-2010 11:55 AM

I am trying to calculate the number of months between to SAS date values using the intck function. However, I do not want the end result to be rounded. For example instead of 33.0 I would like to see 32.967. Is there another function I could use to get this result? Any help with this would be appreciated.

Thanks

Thanks

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

Posted in reply to LAtwood

01-26-2010 12:56 PM

What type of SAS variable(s) or constants are you using with INTCK - share code, ideally?

Scott Barry

SBBWorks, Inc.

Scott Barry

SBBWorks, Inc.

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

01-26-2010 01:08 PM

Scott here's the code I'm working with. yrs_service is the calculation - Ideally I would like to display a decimal format

PROC SQL;

CREATE TABLE SASUSER.QUERY_FOR_PENSION_MSTR_0006 AS SELECT DISTINCT (intck('month',PENSION_MSTR.hire_dt ,(input(substr("&retire_date",3,6),yymmdd.))) ) AS yrs_service

FROM PAY.PENSION_MSTR AS PENSION_MSTR

WHERE PENSION_MSTR.clock_num = "&clock_num";

QUIT;

PROC SQL;

CREATE TABLE SASUSER.QUERY_FOR_PENSION_MSTR_0006 AS SELECT DISTINCT (intck('month',PENSION_MSTR.hire_dt ,(input(substr("&retire_date",3,6),yymmdd.))) ) AS yrs_service

FROM PAY.PENSION_MSTR AS PENSION_MSTR

WHERE PENSION_MSTR.clock_num = "&clock_num";

QUIT;

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

Posted in reply to LAtwood

01-26-2010 01:10 PM

Also if I got the right amoung of months I would then obviously divide by twelve to get the years.

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

Posted in reply to LAtwood

01-26-2010 01:11 PM

Also if I got the right amoung of months I would then obviously divide by twelve to get the years.

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

Posted in reply to LAtwood

01-26-2010 01:28 PM

You will need to demonstrate now SAS is returning a non-integer value from the INTCK function with MONTH as the first argument. A SAS log with source and resolved macro variables revealed, such as adding:

OPTIONS SOURCE SOURCE2 MGEN SGEN MPRINT;

Scott Barry

SBBWorks, Inc.

OPTIONS SOURCE SOURCE2 MGEN SGEN MPRINT;

Scott Barry

SBBWorks, Inc.

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

01-26-2010 01:40 PM

1 The SAS System 13:05 Tuesday, January 26, 2010

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code1);

4 %LET _EGTASKLABEL=%NRBQUOTE(Code1);

5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(D:\SASDM\TSSUser\tssl.adams\egpuser\Oracle\Pension_Plan.egp);

6 %LET _SASPROGRAMFILE=;

7

8 ODS _ALL_ CLOSE;

NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these

drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.

9 OPTIONS DEV=ACTIVEX;

NOTE: Writing ODS PDF(EGPDF) output to TEMP destination "E:\SASWORK\_TD1804\#LN00023", printer "PDF".

10 FILENAME EGPDF TEMP;

11 ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=printer SAS;

SYMBOLGEN: Macro variable SYSVLONG resolves to 9.01.01M3P020206

SYMBOLGEN: Macro variable GRAPHAVAIL resolves to 9.01 TS1M3

12

13 %gaccessible;

SYMBOLGEN: Macro variable ACCESSIBLE resolves to ACCESSIBLE

MPRINT(GACCESSIBLE): GOPTIONS ACCESSIBLE;

14 OPTIONS SOURCE SOURCE2 MGEN SGEN MPRINT;

15 PROC SQL;

16 CREATE TABLE SASUSER.QUERY_FOR_PENSION_MSTR_0007 AS SELECT DISTINCT (intck('month',

17 PENSION_MSTR.hire_dt ,

18 (input(substr("&retire_date",

SYMBOLGEN: Macro variable RETIRE_DATE resolves to 20100101

SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

19 3,

20 6),

21 yymmdd.))) ) AS yrs_service

22 FROM PAY.PENSION_MSTR AS PENSION_MSTR

23 WHERE PENSION_MSTR.clock_num = "&clock_num";

SYMBOLGEN: Macro variable CLOCK_NUM resolves to 65310

SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

NOTE: Table SASUSER.QUERY_FOR_PENSION_MSTR_0007 created, with 1 rows and 1 columns.

24

25 QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 1.37 seconds

cpu time 0.06 seconds

26

27 %LET _CLIENTTASKLABEL=;

28 %LET _EGTASKLABEL=;

29 %LET _CLIENTPROJECTNAME=;

30 %LET _SASPROGRAMFILE=;

31

32 ;*';*";*/;quit;run;

33 ODS _ALL_ CLOSE;

NOTE: ODS PDF(EGPDF) printed no output.

(This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE statement.)

34

35

36 QUIT; RUN;

37

Scott I am unsure where it states in the log the value from the INTCK function. Or if it even displays it at all?

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code1);

4 %LET _EGTASKLABEL=%NRBQUOTE(Code1);

5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(D:\SASDM\TSSUser\tssl.adams\egpuser\Oracle\Pension_Plan.egp);

6 %LET _SASPROGRAMFILE=;

7

8 ODS _ALL_ CLOSE;

NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these

drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.

9 OPTIONS DEV=ACTIVEX;

NOTE: Writing ODS PDF(EGPDF) output to TEMP destination "E:\SASWORK\_TD1804\#LN00023", printer "PDF".

10 FILENAME EGPDF TEMP;

11 ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=printer SAS;

SYMBOLGEN: Macro variable SYSVLONG resolves to 9.01.01M3P020206

SYMBOLGEN: Macro variable GRAPHAVAIL resolves to 9.01 TS1M3

12

13 %gaccessible;

SYMBOLGEN: Macro variable ACCESSIBLE resolves to ACCESSIBLE

MPRINT(GACCESSIBLE): GOPTIONS ACCESSIBLE;

14 OPTIONS SOURCE SOURCE2 MGEN SGEN MPRINT;

15 PROC SQL;

16 CREATE TABLE SASUSER.QUERY_FOR_PENSION_MSTR_0007 AS SELECT DISTINCT (intck('month',

17 PENSION_MSTR.hire_dt ,

18 (input(substr("&retire_date",

SYMBOLGEN: Macro variable RETIRE_DATE resolves to 20100101

SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

19 3,

20 6),

21 yymmdd.))) ) AS yrs_service

22 FROM PAY.PENSION_MSTR AS PENSION_MSTR

23 WHERE PENSION_MSTR.clock_num = "&clock_num";

SYMBOLGEN: Macro variable CLOCK_NUM resolves to 65310

SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

NOTE: Table SASUSER.QUERY_FOR_PENSION_MSTR_0007 created, with 1 rows and 1 columns.

24

25 QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 1.37 seconds

cpu time 0.06 seconds

26

27 %LET _CLIENTTASKLABEL=;

28 %LET _EGTASKLABEL=;

29 %LET _CLIENTPROJECTNAME=;

30 %LET _SASPROGRAMFILE=;

31

32 ;*';*";*/;quit;run;

33 ODS _ALL_ CLOSE;

NOTE: ODS PDF(EGPDF) printed no output.

(This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE statement.)

34

35

36 QUIT; RUN;

37

Scott I am unsure where it states in the log the value from the INTCK function. Or if it even displays it at all?

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

01-26-2010 02:07 PM

Scott,

I have found the yrdif() function. This provides a decimal format. Thanks for your help.

Lori

I have found the yrdif() function. This provides a decimal format. Thanks for your help.

Lori

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

Posted in reply to LAtwood

01-28-2010 04:58 PM

Lori,

I think you've found out that the INTCK function only returns integer values. This is because it counts period boundaries, and not durations. For example:

INTCK('MONTH', '30Mar2009'd, '05May2009'd)

= INTCK('MONTH', '05Mar2009'd, '30May2009'd)

= 2

because it has counted 2 month boundaries on 01Apr2009 and 01May2009 in each case.

In the same way the INTNX function increments dates to the corresponding boundary date. For example:

INTNX('MONTH', '05Mar2009'd, 2)

= INTNX('MONTH', '30Mar2009'd, 2)

= '01May2009'd

Hope this clears up any confusion..........Phil

I think you've found out that the INTCK function only returns integer values. This is because it counts period boundaries, and not durations. For example:

INTCK('MONTH', '30Mar2009'd, '05May2009'd)

= INTCK('MONTH', '05Mar2009'd, '30May2009'd)

= 2

because it has counted 2 month boundaries on 01Apr2009 and 01May2009 in each case.

In the same way the INTNX function increments dates to the corresponding boundary date. For example:

INTNX('MONTH', '05Mar2009'd, 2)

= INTNX('MONTH', '30Mar2009'd, 2)

= '01May2009'd

Hope this clears up any confusion..........Phil

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

Posted in reply to LAtwood

01-28-2010 06:34 PM

Lori

yrdif() is documented as serving a fairly narrow purpose. http://support.sas.com/kb/36/977.html

Would the simple calculation

( PENSION_MSTR.hire_dt - "&retire_date"d ) / 365.25

provide a logically correct number for "years with decimal fraction" ?

yrdif() is documented as serving a fairly narrow purpose. http://support.sas.com/kb/36/977.html

Would the simple calculation

( PENSION_MSTR.hire_dt - "&retire_date"d ) / 365.25

provide a logically correct number for "years with decimal fraction" ?

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

Posted in reply to Peter_C

01-28-2010 07:33 PM

A constant of 365.25 - divisor for computing "logically correct number of years"? For real? The appropriate function is INTCK. Better to suggest the OP (Lori) treat the problem rather than attempting to circumvent or treat the perceived symptom which has not yet been confirmed. Come on guys!

Scott Barry

SBBWorks, Inc.

Scott Barry

SBBWorks, Inc.

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

01-29-2010 03:13 AM

Scott,

what are you offering the OP whose subject line seeks to avoid the ingeter result generated by the technique you suggest?

what are you offering the OP whose subject line seeks to avoid the ingeter result generated by the technique you suggest?

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

Posted in reply to Peter_C

01-29-2010 09:23 AM

Here is the code for an accurate decimal months interval calculation using SAS functions:

1 data _null_;

2 format s_dt l_dt l_dt_end date9.;

3 s_dt = mdy(12,25,2008);

4 l_dt = mdy(1,15,2010);

5 months = intck('month',s_dt,l_dt);

6 l_dt_end = intnx('month',l_dt,0,'e');

7 dec_months = months + (day(l_dt) / day(l_dt_end));

8 putlog _all_;

9 run;

s_dt=25DEC2008 l_dt=15JAN2010 l_dt_end=31JAN2010 months=13 dec_months=13.483870968

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.01 seconds

Scott Barry

SBBWorks, Inc.

TS-668 - SAS Dates, Times, and Interval Functions

http://support.sas.com/techsup/technote/ts668.pdf

1 data _null_;

2 format s_dt l_dt l_dt_end date9.;

3 s_dt = mdy(12,25,2008);

4 l_dt = mdy(1,15,2010);

5 months = intck('month',s_dt,l_dt);

6 l_dt_end = intnx('month',l_dt,0,'e');

7 dec_months = months + (day(l_dt) / day(l_dt_end));

8 putlog _all_;

9 run;

s_dt=25DEC2008 l_dt=15JAN2010 l_dt_end=31JAN2010 months=13 dec_months=13.483870968

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.01 seconds

Scott Barry

SBBWorks, Inc.

TS-668 - SAS Dates, Times, and Interval Functions

http://support.sas.com/techsup/technote/ts668.pdf

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

01-29-2010 09:38 AM

Scott,

how would you refine it for the case [pre]1646 data _null_;

1647 format s_dt l_dt l_dt_end date9.;

1648 s_dt = mdy(12,25,2008);

1649 l_dt = mdy(12,25,2010);

1650 months = intck('month',s_dt,l_dt);

1651 l_dt_end = intnx('month',l_dt,0,'e');

1652 dec_months = months + (day(l_dt) / day(l_dt_end));

1653 putlog _all_;

1654 run;

s_dt=25DEC2008 l_dt=25DEC2010 l_dt_end=31DEC2010 months=24 dec_months=24.806451613 _ERROR_=0 _N_=1

NOTE: DATA statement used[/pre] where I think the dec_months should result in 24 rather than dec_months=24.806451613

how would you refine it for the case [pre]1646 data _null_;

1647 format s_dt l_dt l_dt_end date9.;

1648 s_dt = mdy(12,25,2008);

1649 l_dt = mdy(12,25,2010);

1650 months = intck('month',s_dt,l_dt);

1651 l_dt_end = intnx('month',l_dt,0,'e');

1652 dec_months = months + (day(l_dt) / day(l_dt_end));

1653 putlog _all_;

1654 run;

s_dt=25DEC2008 l_dt=25DEC2010 l_dt_end=31DEC2010 months=24 dec_months=24.806451613 _ERROR_=0 _N_=1

NOTE: DATA statement used[/pre] where I think the dec_months should result in 24 rather than dec_months=24.806451613

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

01-29-2010 10:38 AM

Scott,

The month difference should be closer to 12.6 than 13.4. I think the yrdif function still gets me closer to the figure I need. When I calculate using the yrdif function I get 12.6. I understand there still is a discrepancy with the yrdif function but unless there are no other options with intck to get a decimal the yrdif might be my only option.

From 12-25-08 to 12-25-09 is one year difference. 6 days left in december, and 15 days in january the following year, add up to 21 days. Divide 21 by 31 days will give you .677. So one year plus .677 will give you 12.677. So how do you get 13.4?

The month difference should be closer to 12.6 than 13.4. I think the yrdif function still gets me closer to the figure I need. When I calculate using the yrdif function I get 12.6. I understand there still is a discrepancy with the yrdif function but unless there are no other options with intck to get a decimal the yrdif might be my only option.

From 12-25-08 to 12-25-09 is one year difference. 6 days left in december, and 15 days in january the following year, add up to 21 days. Divide 21 by 31 days will give you .677. So one year plus .677 will give you 12.677. So how do you get 13.4?