topic Re: DI Studio: convert ccyymmdd numeric to datetime in SAS Data Management

DI Studio: convert ccyymmdd numeric to datetime

cnilsen — Tue, 07 Mar 2017 20:46:14 GMT

Hello SAS world,

I'm working with DI Studio (v4.901) to import flat files, perform some minor editing and sorting, and append the results into a permanant file where Dataflux will take the records and standardize the data.

My date column in question is sent as CCYYMMDD. (20170119)

I'm importing it as length 8, informat yymmdd8.0, format date9.

So.. my results are 19JAN2017.

My final transformation is a table loader (appending records) into a end result table.

By the end of my process, I need to change this date value into a datetime22 format to match the end result table.

I expect the 'time' value to be 0, but the date to be in the date9. format still. (19JAN2017:00:00:00)

Can I do this by changing the mapping at time of the table import.. or in one of my transformations such as the table loader?

Any helpful suggestions are apppreciated,

Chris N.

Re: DI Studio: convert ccyymmdd numeric to datetime

Kurt_Bremser — Tue, 07 Mar 2017 20:56:05 GMT

Assign the format and use dhms() to convert to a datetime value:

date = dhms(date,0,0,0);

Re: DI Studio: convert ccyymmdd numeric to datetime

ballardw — Tue, 07 Mar 2017 20:58:43 GMT

And assign the desired format: format date datetime22. ;

Re: DI Studio: convert ccyymmdd numeric to datetime

cnilsen — Tue, 07 Mar 2017 20:59:37 GMT

Would this be easiest at the time I am initially reading the flat file?

Re: DI Studio: convert ccyymmdd numeric to datetime

Patrick — Tue, 07 Mar 2017 21:55:08 GMT

You could use informat anydtdtm. and convert the source date string to a SAS datetime value when reading the flat file.

%let save_datestyle=%sysfunc(getoption(datestyle));
options datestyle=ymd;
data test;
  format my_dttm datetime22.;
  my_dttm=input('20170119',anydtdtm.);
run;
options datestyle=&save_datestyle;

The anydt... informats ingest different date string patterns. This bears the risk that for your data an invalid date string gets converted to a SAS Datetime value without warning; i.e. a date string of 20171901 which would be invalid for you but which would get read by the anydt... informat assuming it's a date string in a YDM pattern.

Your choice!

Re: DI Studio: convert ccyymmdd numeric to datetime

Kurt_Bremser — Wed, 08 Mar 2017 15:06:22 GMT

@cnilsen wrote:
Would this be easiest at the time I am initially reading the flat file?

Why not? The important thing is to be consistent about datatypes, so you don't get unexpected results by comparing dates to datetimes.

So yes, implement the conversion when reading the flat file.

Re: DI Studio: convert ccyymmdd numeric to datetime

cnilsen — Wed, 08 Mar 2017 15:38:05 GMT

I still cannot get my head around this. Everything I have tried so in a transformation far WILL create a date/timestamp. But it always defaults my date to 1JAN1960. and time value 00:00:01. I'm at a point where I'm going to go back to the As400 system and add a blank time value to the outbound table I'm going to process later with DI.

I also tried this variations of this, which resulted in a null value for the column:

Re: DI Studio: convert ccyymmdd numeric to datetime

LinusH — Wed, 08 Mar 2017 17:45:03 GMT

You haven't shown what you have actually tried.
It sounds like you have successfully imported the cyymmdd to a SAS numeric column with a date. format and it's showing the correct date?
Then, in the table loader use the dhms function as suggested by @Kurt_Bremser, as a mapping expression.
If you tried that, show the generated step code.

Re: DI Studio: convert ccyymmdd numeric to datetime

cnilsen — Wed, 08 Mar 2017 17:53:14 GMT

I'm putting together a document to upload what I have so far...

Re: DI Studio: convert ccyymmdd numeric to datetime

RLigtenberg — Wed, 08 Mar 2017 18:07:00 GMT

See if this helps. Uses mock data to resemble your scenario.

Regards,

Robert

Re: DI Studio: convert ccyymmdd numeric to datetime

cnilsen — Wed, 08 Mar 2017 18:11:16 GMT

I like this.. I'll work on it and get back to you.!

-Chris.

Re: DI Studio: convert ccyymmdd numeric to datetime

RLigtenberg — Wed, 08 Mar 2017 18:23:07 GMT

Page 2, just in case...only page 1 shows in my browser.

Re: DI Studio: convert ccyymmdd numeric to datetime

cnilsen — Wed, 08 Mar 2017 18:46:40 GMT

Robert..

Success at last !.

I was close to this in my expression building, but I had the formats all wrong, so my results were never correct.

I appreciate taking the time to create a document to show me how you did this. It was extremely helpful, and now gets added to many other help docs I have accumulated for future reference.

Regards and Thanks,

Chris.