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

Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

I'm working on a job and need to convert a character date (yyyy-mm-dd) from a source table over to a "mm/dd/yyyy" format in the target table.  I'm not saavy enough yet to make this edit in the code, so I'm trying to do everything in the Expression Builder for now until I'm a little more well versed.


Accepted Solutions
Solution
‎02-26-2016 02:02 PM
Super User
Super User
Posts: 7,050

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Posted in reply to Daylon_Hunt

Well if the character string has the year first then trying to read it using MMDDYY10. informat will of course generate an error. There is no month number 2009!  Use YYMMDD10. informat (note do not need to specify the N,S,D letter in the INFORMAT that you would need in the FORMAT).

 

View solution in original post


All Replies
PROC Star
Posts: 1,167

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Posted in reply to Daylon_Hunt

I think that two date functions will do it for you:

 

OutDate = put(input(InDate, yymmdd10.), mmddyys10.);

Contributor
Posts: 20

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Works perfect Tom, thank you kindly 

Contributor
Posts: 20

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Actually, that's giving me the wrong digits for the year.  For example, a "June 29 2010" date is coming out like: 06/29/20

PROC Star
Posts: 1,167

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Posted in reply to Daylon_Hunt

That's REALLY weird! Unfortunately, I don't have DI Studio available, I just mocked this up in Base SAS. If I run this program:

 

data Dates;

InDate = '2010-06-29';

OutDate = put(input(InDate, yymmdd10.), mmddyys10.);

output;

run;

 

the output is:

 

InDate

OutDate

2010-06-29

06/29/2010

 

 

Contributor
Posts: 20

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

You're actually correct, except it keeps it as a Character type when it is preferred to keep a date in Numeric.  So what I'm left with is:   OutDate = INPUT(InDate,mmddyys10.)   but that's generating an error for me and so I'm back to square one.

Solution
‎02-26-2016 02:02 PM
Super User
Super User
Posts: 7,050

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Posted in reply to Daylon_Hunt

Well if the character string has the year first then trying to read it using MMDDYY10. informat will of course generate an error. There is no month number 2009!  Use YYMMDD10. informat (note do not need to specify the N,S,D letter in the INFORMAT that you would need in the FORMAT).

 

Super User
Posts: 5,429

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Posted in reply to Daylon_Hunt
The input() is working. But it's not a best practice to store dates as char. They are morw complicated to handle, and does rarely work with exteenal dbms/applications. Hence skip the put().
Data never sleeps
Contributor
Posts: 20

Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio

Ok, it's for sure solved this time.  Keeping it in a Numeric type and using the expression:

 

OutDate = INPUT(InDate , yymmdd10.)  

AND

A Format of "mmddyy10."

 

Whew!  Thank you both!

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 1341 views
  • 3 likes
  • 4 in conversation