03-07-2017 03:46 PM
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,
03-07-2017 03:56 PM
03-07-2017 04:55 PM
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.
03-08-2017 10:06 AM
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.
03-08-2017 10:38 AM
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:
03-08-2017 01:46 PM
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,
03-08-2017 12:45 PM
Need further help from the community? Please ask a new question.