topic Re: Datepart Problem in SAS Enterprise Guide

Datepart Problem

deleted_user — Wed, 14 Jan 2009 19:13:02 GMT

I am trying to convert a number to a date in EG and it is not working, Could you help me out:

I know that the number that is stored in the SAS field is in milliseconds.

Example of what is in the field:
253402146000000 want it converted to 12/30/9999
1228712400000 want it converted to 12/8/2008

Here is an example of what I am getting:
253402146000000 What I am geting 12/31/9989
1228712400000 What I am geting 12/8/1998

When I use DATEPART(BASEL_BL_L.ACTIVE_TO_DTTM /1000) in a calculated field the date is off by 10 years. I beleive that is happening because DATEPART uses 1/1/1960 to figure out the date, but this field uses 1/1/1970.

How do I go about doing this?

Thanks,

Ryan

-------------Edit--------------------

Also here is the code that is running.... Very simple:

PROC SQL;
CREATE TABLE SASUSER.DATASET_FOR_VENDOR_REF AS SELECT BASEL_BL_L.SOURCE_SYSTEM_CD FORMAT=$3.,
BASEL_BL_L.BASEL_BL_ID FORMAT=$32.,
BASEL_BL_L.BASEL_BL_RK FORMAT=20.,
BASEL_BL_L.ACTIVE_TO_DTTM FORMAT=20.,
(DATEPART(BASEL_BL_L.ACTIVE_TO_DTTM /1000)
) FORMAT=MMDDYYS10.0 AS VendorActiveToDate
FROM SASOPRSK.BASEL_BL_L AS BASEL_BL_L
WHERE BASEL_BL_L.SOURCE_SYSTEM_CD = "SMS";
QUIT; Message was edited by: rtreptow

Re: Datepart Problem

sbb — Wed, 14 Jan 2009 19:54:24 GMT

Subtract 10 years with the following constant generated using the SAS MDY function, which creates a SAS-internal number representing 10 years, given that 1/1/1960 is a value of 0:

MDY(1,1,1970)

Scott Barry
SBBWorks, Inc.

Re: Datepart Problem

deleted_user — Wed, 14 Jan 2009 20:35:44 GMT

The problem is I need to add Ten years to the dates for Datepart.

So would I need to do something like this:

DATEPART(BASEL_BL_L.ACTIVE_TO_DTTM /1000) + MDY(1,1,1970)

Thanks from the new guy,

Ryan

Re: Datepart Problem

deleted_user — Thu, 15 Jan 2009 08:58:44 GMT

just tried (the following is a snip from a sas log):[pre]5 * 253402146000000 want it converted to 12/30/9999 ;
6 %let num =
7 253402146000000
8 ;
9 %put %sysevalf( "30dec9989"d +0 );
2932894
10 %put %sysfunc( putn( &num/3600/24/1000, date9.));
31DEC9989[/pre]
So, only need to add 10 years. However, since that is not a multiple of 4, leap year days make the "10-year" adjustment subject to some more rules,
that I don't see!

If I am to add 10 years (in milliseconds), how many leap days are appropriate?

PeterC forgot: also need to deduct a day (at least that is a constant number of millisecs)!

Message was edited by: Peter_c

Re: Datepart Problem

deleted_user — Thu, 15 Jan 2009 17:03:50 GMT

I did get it to work, here is what I did.

MDY(Month(CALCULATED Datepart),Day(CALCULATED Datepart),Year(CALCULATED Datepart)+10 )

Thanks sbb for helping me figure this out.

The calculated field for Datepart was:
DATEPART(BASEL_BL_L.ACTIVE_TO_DTTM/1000 )

So here is the whole code I did:

PROC SQL;
CREATE TABLE SASUSER.DATASET_FOR_VENDOR_REF AS SELECT BASEL_BL_L.SOURCE_SYSTEM_CD FORMAT=$3.,
BASEL_BL_L.BASEL_BL_ID FORMAT=$32.,
BASEL_BL_L.BASEL_BL_RK FORMAT=20.,
BASEL_BL_L.ACTIVE_TO_DTTM FORMAT=20.,
(DATEPART(BASEL_BL_L.ACTIVE_TO_DTTM/1000 )) FORMAT=MMDDYY10.0 AS Datepart,
(MDY(Month(CALCULATED Datepart),
Day(CALCULATED Datepart),
Year(CALCULATED Datepart)+10 )
) FORMAT=MMDDYY10.0 AS VendorActiveToDate2
FROM SASOPRSK.BASEL_BL_L AS BASEL_BL_L
WHERE BASEL_BL_L.SOURCE_SYSTEM_CD = "SMS";
QUIT;

Thanks everyone..

Ryan

Re: Datepart Problem

deleted_user — Fri, 16 Jan 2009 08:53:09 GMT

if a DATEPART was 29-Feb of some leap year, what should the 10 year adjustment be ?

just curious

Re: Datepart Problem

sbb — Fri, 16 Jan 2009 12:27:44 GMT

The SAS MDY function will handle leap-year appropriately. Stick with using SAS functions and you will happily get the correct answers when using date and datetime logic processing. Other functions such as INTNX, INTCK, also play well with leap-year conditions.

Scott Barry
SBBWorks, Inc.

Re: Datepart Problem

deleted_user — Fri, 16 Jan 2009 12:50:05 GMT

dup dup

Message was edited by: Peter_c

Re: Datepart Problem

deleted_user — Fri, 16 Jan 2009 12:51:42 GMT

Scott

does the MDY() function handle changing the day when adding or subtracting 10 from the year value for a 29-Feb date?
This SASlog snip shows a simple demo of the problem is see with the MDY() approach[pre]203 %put %sysfunc( mdy( 2,29,2008), date9 );
29FEB2008
204 %put %sysfunc( mdy( 2,29,2018), date9 );
WARNING: An argument to the function MDY referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set to a missing value.
[/pre]
Adapting the number of milliseconds works, until adding 10 years, an interval which can have 2 or 3 leap days. Some help comes from the SAMEDAY parameter of the INTNX() function for interval handling.
However, I assume/hope the system, from which these milisecond timestamps come, will have defined the way it handles leap days.

or do I just mis-understand ~ should we just add the (constant) number of days or milliseconds between base-dates for SAS (1Jan1960) and the other system (1Jan1970)?

PeterC doh'
so that was what you were suggesting with mdy(1,1,1970) !
ahhhh

Message was edited by: Peter_c

Re: Datepart Problem

sbb — Fri, 16 Jan 2009 18:53:49 GMT

SAS is expecting you to assign a numeric variable with either seconds (DATETIME) or days (DATE type), and a value of zero represents 1/1/1960. And there are minimum and maximum value range for SAS handling of date/datetime values, to consider.

Scott Barry
SBBWorks, Inc.

SAS Language Concepts - discussion on Dates, Times and Intervals (Chapter 8):
http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_913/base_lrconcept_9196.pdf