BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
9 REPLIES 9
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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
deleted_user
Not applicable
if a DATEPART was 29-Feb of some leap year, what should the 10 year adjustment be ?

just curious
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
dup dup


Message was edited by: Peter_c
deleted_user
Not applicable
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1592 views
  • 0 likes
  • 2 in conversation