topic Re: Intck function (is it possible to not round number and display decimal?) in SAS Procedures

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

LAtwood — Tue, 26 Jan 2010 16:55:50 GMT

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

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

sbb — Tue, 26 Jan 2010 17:56:36 GMT

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

Scott Barry
SBBWorks, Inc.

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

LAtwood — Tue, 26 Jan 2010 18:08:52 GMT

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;

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

LAtwood — Tue, 26 Jan 2010 18:10:42 GMT

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

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

LAtwood — Tue, 26 Jan 2010 18:11:07 GMT

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

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

sbb — Tue, 26 Jan 2010 18:28:02 GMT

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.

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

LAtwood — Tue, 26 Jan 2010 18:40:59 GMT

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?

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

LAtwood — Tue, 26 Jan 2010 19:07:48 GMT

Scott,

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

Lori

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

prholland — Thu, 28 Jan 2010 21:58:19 GMT

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

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

Peter_C — Thu, 28 Jan 2010 23:34:36 GMT

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

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

sbb — Fri, 29 Jan 2010 00:33:27 GMT

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.

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

Peter_C — Fri, 29 Jan 2010 08:13:37 GMT

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

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

sbb — Fri, 29 Jan 2010 14:23:27 GMT

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

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

Peter_C — Fri, 29 Jan 2010 14:38:16 GMT

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

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

LAtwood — Fri, 29 Jan 2010 15:38:06 GMT

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?

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

sbb — Fri, 29 Jan 2010 15:40:56 GMT

SAS made the calculation - not me -- you will need to take it from here. Suggest you follow the SAS function logic in the DOC, then adapt your increment/decrement choice based on SAS-generated results. My expectation is that you will see consistency using the function, but it's your choice to go up or down, as desired, with your SAS application needs.

Scott Barry
SBBWorks, Inc.

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

LAtwood — Fri, 29 Jan 2010 15:46:31 GMT

Scott,

Thanks for your help and input on this. I appreciate it.

Lori

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

Cynthia_sas — Fri, 29 Jan 2010 16:54:32 GMT

Hi:
Using either a modified version of Scott's method or a modified version of Peter's method, it is possible to either come CLOSE to your 12.6 or exactly hit your 12.677. See the attached modified program and log output. If all you wanted was the 12.6, then either of the modified methods will give you that number. If you want -exactly- 12.677...

cynthia
[pre]
467 data _null_;
468 format s_dt l_dt l_dt_end s_dt_end l_dt_beg date9.;
469 s_dt = mdy(12,25,2008);
470 l_dt = mdy(1,15,2010);
471
472 ** Scott B Original Calc;
473 months = intck('month',s_dt,l_dt);
474 l_dt_end = intnx('month',l_dt,0,'e');
475 dec_month_sb_orig = months + day(l_dt) / day(l_dt_end);
476
477 ** Peter C. Suggestion Modified;
478 dec_months_pc = (l_dt - s_dt) /(365.25/12);
479
480 ** get the END of the start date and the BEGIN of the Last date;
481 s_dt_end = intnx('month',s_dt,0,'e');
482 l_dt_beg = intnx('month',l_dt,0,'b');
483
484 ** because INTCK returns whole intervals, subtract 1 day from the;
485 ** beginning of the last date -- this will give you 12 months instead of 13;
486 newmonth = intck('month',s_dt,l_dt_beg-1);
487
488 ** Now, figure out how many days from the start day to the end of the start month;
489 ** and the number of days in the last date;
490 daysleft = (s_dt_end -s_dt) + day(l_dt);
491
492 ** Do the modified formula using DAYSLEFT and NEWMONTH;
493 dec_month_sb_alt = newmonth + (daysleft / day(l_dt_end));
494
495 putlog s_dt= s_dt_end= l_dt= l_dt_beg= l_dt_end=;
496 putlog '***** ***** ***** *****';
497 putlog "Scott Original Calc:";
498 putlog dec_month_sb_orig=;
499 putlog '***** ***** ***** *****';
500 putlog "Peter Suggestion Modified";
501 putlog dec_months_pc=;
502 putlog '***** ***** ***** *****';
503 putlog "Using DAYSLEFT by modifying Scott's Original Calc";
504 putlog daysleft= newmonth= dec_month_sb_alt=;
505 run;

s_dt=25DEC2008 s_dt_end=31DEC2008 l_dt=15JAN2010 l_dt_beg=01JAN2010 l_dt_end=31JAN2010
***** ***** ***** *****
Scott Original Calc:
dec_month_sb_orig=13.483870968
***** ***** ***** *****
Peter Suggestion Modified
dec_months_pc=12.681724846
***** ***** ***** *****
Using DAYSLEFT by modifying Scott's Original Calc
daysleft=21 newmonth=12 dec_month_sb_alt=12.677419355
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[/pre]