topic Re: Converting yyyy-mm-dd to mm/dd/yyyy in DI Studio in SAS Data Management

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

Daylon_Hunt — Thu, 25 Feb 2016 23:17:51 GMT

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.

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

TomKari — Fri, 26 Feb 2016 03:52:48 GMT

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

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

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

LinusH — Fri, 26 Feb 2016 06:44:29 GMT

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().

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

Daylon_Hunt — Fri, 26 Feb 2016 16:07:14 GMT

Works perfect Tom, thank you kindly

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

Daylon_Hunt — Fri, 26 Feb 2016 16:33:37 GMT

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

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

TomKari — Fri, 26 Feb 2016 17:15:02 GMT

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

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

Daylon_Hunt — Fri, 26 Feb 2016 18:33:57 GMT

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.

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

Tom — Fri, 26 Feb 2016 18:54:51 GMT

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

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

Daylon_Hunt — Fri, 26 Feb 2016 18:58:03 GMT

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!