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?
Also here is the code that is running.... Very simple:
CREATE TABLE SASUSER.DATASET_FOR_VENDOR_REF AS SELECT BASEL_BL_L.SOURCE_SYSTEM_CD FORMAT=$3.,
) FORMAT=MMDDYYS10.0 AS VendorActiveToDate
FROM SASOPRSK.BASEL_BL_L AS BASEL_BL_L
WHERE BASEL_BL_L.SOURCE_SYSTEM_CD = "SMS";
Message was edited by: rtreptow
just tried (the following is a snip from a sas log):[pre]5 * 253402146000000 want it converted to 12/30/9999 ;
6 %let num =
9 %put %sysevalf( "30dec9989"d +0 );
10 %put %sysfunc( putn( &num/3600/24/1000, date9.));
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?
forgot: also need to deduct a day (at least that is a constant number of millisecs)!
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.
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 );
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.
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)?
so that was what you were suggesting with mdy(1,1,1970) !
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.