BookmarkSubscribeRSS Feed
EinarRoed
Pyrite | Level 9

Hello,

 

This question is in regards to Data Integration Studio, which there doesn't seem to be a forum for.

 

 I have a basic DATE9 variable, which I need to transform to DATETIME19. Specifically, the value "21SEP2017" should become "21SEP2017:23:59:59".

 

A dirty way to do it is to use this expression: (DATE_DT *24*60*60)+86399

 

However, it's important that all temporary tables are stored in a Netezza library. For the sake of efficiency, this node should also be set to "Create as view". Unfortunately that makes it no longer accept the expression ("ERROR: The create view statement cannot be executed directly against the database because of errors").

 

Do you have any advice on a "Netezza-view-friendly" way to transform the date into datetime?

3 REPLIES 3
Patrick
Opal | Level 21

@EinarRoed

The SAS Data Management forum is focused on tools like DIS.

https://communities.sas.com/t5/SAS-Data-Management/bd-p/data_management

 

As for your question: It looks to me like you're confusing here two concepts.

SAS stores Date and DateTime values as numbers in a numeric variable. The formula you show - DATE_DT *24*60*60 - is a way to convert a SAS Date value to a SAS DateTime value by multiplying the SAS Date value with the number of seconds of a day.

The additional bit - +86399 - looks like an attempt to shift the starting point of such date counts from the SAS to the Netezza value. That's certainly nothing you should try to attempt.

 

The SAS/Access engine will convert the dates for you. The mapping between SAS Date/Datetime and Netezza column types is documented here: 

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p07ge3f7fvfh5ln1msgdpqhbeh31.htm&docsetV...

 

Because SAS doesn't have its own data types for Dates and Times the conversion used is based on the SAS format assigned to a variable.

Make sure you have a format of DATEw. assigned to variables holding SAS Date values and a format of DATETIMEw.d for SAS DateTime values. If you do so then things should just work.  

 

 

LinusH
Tourmaline | Level 20

This is actually a quit interesting question.

If I understand it right, both source and target in your job is within Netezza, and you wish to perform ELT, to avoid data to pass the "remote" SAS session, right?

You can force some transformations to execute explicit pass through code, and I guess that you then could use Netezza specif syntax to transform date to timestamp values. Not so nice from a metadata driven SAS DI perspective, since it will make heavy on specific target DBMS syntax.

The other option is to rely on implicit pass through. In the best of worlds, using formats as mentioned by @Patrick might do the trick. But I haven't tried this scenario, and I suspect that this dosn't either work, or still trigger a data pass via the SAS session.

Data never sleeps
Patrick
Opal | Level 21

@LinusH

When it comes to ETL with all source and target tables in the data base then I've ended up to mostly not use OOTB transformations at all. I found this the only feasible way to ensure that all processing happens within the data base and to allow me to use data base specific syntax.

@EinarRoed

If the OOTB transformations don't let you implement efficient code then consider to create custom transformations for repeated logic using as much of the generated macro variables from this transformation as possible in your code to still end up with something as  metadata driven as possible.

 

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
  • 3 replies
  • 1258 views
  • 0 likes
  • 3 in conversation