Date Format

[marleeakerson]

Hello, 

I am having some trouble with date formatting. I am inputting a table that has a date variable that looks like this: 

But when I try to format it using this code:

format cp_createdat MMDDYY10. ;

I get this in the output table: 

Does anyone why this is/how to fix it? I am hoping to format this variable to be in the MM/DD/YYYY format. 

Thank you!

[Reeza]

You have a DATETIME variable, not a DATE variable. You need to use a DATETIME format, or convert it to a DATE variable. 

DATEPART will convert a datetime variable to a date variable. 

Or you can try adding DT in front of the desired format.

format cp_createdat DTMMDDYY10. ;

Or a full conversion:

cp_create_date = datepart(cp_createdat);
format cp_create_date MMDDYY10. ;

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/...

[PaigeMiller]

format cp_createdat dtdate.;

Not the exact formatting you asked for, but it works.

[Shmuel]

CP_Created is a datetime variable. Use DATEPART() function to extrat the date only from it:

cp_created = datepart(cp_created);
format cp_created mmddyy10. ;

If the input is char type then yo need first to convert it to a numeric sas date variable:

date = input(scan(cp_created,1,':'),date7.);
format date mmddyy10.;

[ballardw]

The reason you see ********** is that the underlying numeric value is larger than is expected by the MMDDYY format by about a factor of 86,400. Dates are measured by days in SAS and  datetime values like you have are measured in seconds. So one "day" of a date time is 86,400 seconds and way to large for consideration as a date.

You need to talk to whoever is creating that file and beat them severely with wet spaghetti noodles for using a 2-digit year.

Second, the values you show represent what I call the "lazy Microsoft office programmer" of allowing a "date" to default to a datetime field with 0 hours, 0 minutes and 0 seconds. Useless and ugly if there is not actual time component used.