11-27-2016 05:17 PM
We are using an Extract Transformation in SAS DI to extract data from Teradata to a file. One of the columns is a timestamp and in Teradata the format is YYYY-MM-DD HH:MMS. The default SAS timestamp format returns the value in format like 30MAY2016:21:32:32 or yyyy-mm-ddThh:mm:ss with a ‘T’ between the Date and Hour. Couple of options that we have tried are
1) Put in Proc format step in the precode of the job as below and use the format in the DI user written code.
picture ymdhms other='%0Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
2) Cast the value to character data type to display it in the same format as the database?
Is there any existing SAS Date format that could be used to display the timestamp in the same format as the Teradata Timestamp format i.e, 'YYYY-MM-DD HH-MM-SS'.
11-27-2016 07:14 PM
Your question has HH:MMS but the last portion of your question has HH-MM-SS which one are you looking for?
Typically, it's HH:MMS.
I don't think there's a standard one in the format you'd like. There is likely 2 together, one for the date portion and one for the time portion that would work, but not sure about a datetime format.
You can review all the formats here, and they're organized by category to help you filter the date/datetime formats. The IS 8601 section is also date & time formats.
11-27-2016 07:27 PM
I am looking for the output format YYYY-MM-DD HH:MMS. In the link the closest one is
But it has a 'T' between the Date and Time part.
11-27-2016 07:32 PM
Then the options you've outlined are the best alternatives, either creating your own or using a combination of date & time formats and converting to character.
I'm not sure there's anything else we can suggest. If it doesn't exist, it doesn't exist.
11-27-2016 10:42 PM - edited 11-27-2016 10:43 PM
Actually: The Timestamp datatype could also contain fractional seconds. If this is the case and given the usual precision challenges when transfering fractional values between platforms, I'd go for an approach where you cast the timestamp on the Teradata side to a string and then load the string to SAS.
02-25-2017 04:49 AM
This below proc format will not work when you have date like "12/11/1969"(dd/mm/yyyy) so this will generate putput like "1969-12-11 00:00:00" and for date "13/11/1969" it will generate out " 1969-11-13 00:00:00"
other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)
here for inout "12/11/1969"(dd/mm/yyyy it should generate output "1969-11-12 00:00:00"
02-25-2017 09:56 AM
The variable you're showing is a date but the format is datetime format.
It looks like you should first create a datetime variable using DHMS() function.
New_var = DHMS(old_var, 0, 0, 0);
format new_var dtpic.;
04-28-2017 02:19 PM
Sorry if this is too late to the party, but the format you're looking for is DATETIME20.9
If you need an AM/PM in the date you can use DATEAMPM. (DATEAMPM13. for 13 characters, ect.)
The one thing I haven't been able to put together is how to get it to show it as DDMMYYYY. Seems to only do DDMMMYY (With the MMM being text).