topic Re: Timestamp format of YYYY-MM-DD HH:MM:SS in SAS Data Management

Timestamp format of YYYY-MM-DD HH:MM:SS

JM_VFAU — Wed, 30 Aug 2017 16:22:30 GMT

Hi,

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:MM:SS

. 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.

proc format;
  picture ymdhms other='%0Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
run;

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'.

John

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Reeza — Mon, 28 Nov 2016 00:14:04 GMT

Your question has HH:MM:SS but the last portion of your question has HH-MM-SS which one are you looking for?

Typically, it's HH:MM:SS.

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.

https://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#n0p2fmevfgj470n17h4k9f27qjag.htm

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

JM_VFAU — Mon, 28 Nov 2016 00:27:53 GMT

Hi Rezza,

I am looking for the output format YYYY-MM-DD HH:MM:SS. In the link the closest one is

$N8601EXw.dFormat

But it has a 'T' between the Date and Time part.

Thanks

John

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Reeza — Mon, 28 Nov 2016 00:32:38 GMT

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.

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Reeza — Mon, 28 Nov 2016 00:33:11 GMT

You can add a suggestion to SAS Idea's for it to be added in a future release.

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

JM_VFAU — Mon, 28 Nov 2016 01:52:40 GMT

Thanks Reeza.

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Patrick — Wed, 30 Aug 2017 16:39:48 GMT

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.

Editor's note: another reader had a similar question and used CAST in PROC SQL passthru code to Teradata to reconcile date comparisons.

And as @arpitagarwal512 shared, this picture format can produce the original requested output:

proc format;
picture dtpic
other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)
;
run;
data a;
 a = datetime();
 format a dtpic.;
run;

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

JM_VFAU — Mon, 28 Nov 2016 03:55:13 GMT

Thanks Patrick. Suggested the same to development team.

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

arpitagarwal512 — Sat, 25 Feb 2017 09:49:29 GMT

Hi Team,

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"

proc format;
picture dtpic
other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)
;
run;

here for inout "12/11/1969"(dd/mm/yyyy it should generate output "1969-11-12 00:00:00"

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Reeza — Sat, 25 Feb 2017 14:56:24 GMT

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.;

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

XMonsterX — Fri, 28 Apr 2017 18:19:29 GMT

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).

Cheers

Monster

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

JM_VFAU — Sun, 30 Apr 2017 10:17:36 GMT

Thank you.

John