BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JM_VFAU
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

 

See also the complete reference on ISO datetime formats available in SAS.

 

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;

View solution in original post

11 REPLIES 11
Reeza
Super User

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

JM_VFAU
Obsidian | Level 7

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

Reeza
Super User

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.  

Reeza
Super User

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

JM_VFAU
Obsidian | Level 7

Thanks Reeza.

Patrick
Opal | Level 21

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.

 

See also the complete reference on ISO datetime formats available in SAS.

 

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;
JM_VFAU
Obsidian | Level 7

Thanks Patrick. Suggested the same to development team.

arpitagarwal512
Calcite | Level 5

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"

Reeza
Super User

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

XMonsterX
Obsidian | Level 7

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

JM_VFAU
Obsidian | Level 7

Thank you.

 

John

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 91779 views
  • 6 likes
  • 5 in conversation