Intck function (is it possible to not round number and display decimal?)

[LAtwood]

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

[sbb]

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

Scott Barry
SBBWorks, Inc.

[LAtwood]

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;

[LAtwood]

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

[LAtwood]

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

[sbb]

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.

[LAtwood]

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?

[LAtwood]

Scott,

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

Lori

[prholland]

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

[Peter_C]

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" ?

[sbb]

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.

[Peter_C]

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

[sbb]

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

[Peter_C]

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

[LAtwood]

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?