BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Daylon_Hunt
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
TomKari
Onyx | Level 15

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

 

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

Daylon_Hunt
Obsidian | Level 7

Works perfect Tom, thank you kindly 

Daylon_Hunt
Obsidian | Level 7

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

TomKari
Onyx | Level 15

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

 

 

Daylon_Hunt
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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

 

LinusH
Tourmaline | Level 20
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
Daylon_Hunt
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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