SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio: convert ccyymmdd numeric to datetime

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

DI Studio: convert ccyymmdd numeric to datetime

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.

 

 

- Chris N.

Accepted Solutions
Solution
‎03-08-2017 01:41 PM
SAS Employee
Posts: 11

Re: DI Studio: convert ccyymmdd numeric to datetime

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

 

Regards,

Robert

View solution in original post


All Replies
Super User
Posts: 7,854

Re: DI Studio: convert ccyymmdd numeric to datetime

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

date = dhms(date,0,0,0);
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 38

Re: DI Studio: convert ccyymmdd numeric to datetime

Posted in reply to KurtBremser
Would this be easiest at the time I am initially reading the flat file?
- Chris N.
Respected Advisor
Posts: 4,173

Re: DI Studio: convert ccyymmdd numeric to datetime

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!

Super User
Posts: 7,854

Re: DI Studio: convert ccyymmdd numeric to datetime


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 38

Re: DI Studio: convert ccyymmdd numeric to datetime

Posted in reply to KurtBremser

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:

 

 

 

 

 

 

- Chris N.
Super User
Posts: 11,343

Re: DI Studio: convert ccyymmdd numeric to datetime

And assign the desired format: format date datetime22. ;

Solution
‎03-08-2017 01:41 PM
SAS Employee
Posts: 11

Re: DI Studio: convert ccyymmdd numeric to datetime

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

 

Regards,

Robert

Contributor
Posts: 38

Re: DI Studio: convert ccyymmdd numeric to datetime

Posted in reply to RobertLigtenberg
I like this.. I'll work on it and get back to you.!

-Chris.
- Chris N.
SAS Employee
Posts: 11

Re: DI Studio: convert ccyymmdd numeric to datetime

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

Contributor
Posts: 38

Re: DI Studio: convert ccyymmdd numeric to datetime

Posted in reply to RobertLigtenberg

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.

 

 

- Chris N.
Super User
Posts: 5,437

Re: DI Studio: convert ccyymmdd numeric to datetime

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 @KurtBremser, as a mapping expression.
If you tried that, show the generated step code.
Data never sleeps
Contributor
Posts: 38

Re: DI Studio: convert ccyymmdd numeric to datetime

I'm putting together a document to upload what I have so far...
- Chris N.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 515 views
  • 2 likes
  • 6 in conversation