DI Studio: convert ccyymmdd numeric to datetime

[cnilsen]

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.

[Kurt_Bremser]

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

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

[cnilsen]

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

[Patrick]

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!

[Kurt_Bremser]

---

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

[cnilsen]

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:

[ballardw]

And assign the desired format: format date datetime22. ;

[RLigtenberg · Accepted solution]

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

Regards,

Robert

[cnilsen]

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

-Chris.

[RLigtenberg]

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

[cnilsen]

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.

[LinusH]

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.

[cnilsen]

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