Format Datetime to mm/dd/yyy

[Dogo23]

Hi all,

Can anyone explain to me a simple way to format a date with a timestamp to mm/dd/yyy?

Currently the date field show like this: 14NOV2017:17:42:15.000000

I want to format within the select statement

proc sql; 

select marketing_id

format (send_date as.....

etc....

Thanks in advance!

[Dogo23]

Quick update, I meant to write mm/dd/yyyy !

[TomKari]

Okay, it looks like the field send_date in your table is the one that's the problem.

First question...is the data coming into your result as numeric or character?

Tom

[Dogo23]

Hi Tom,

I looked at the table properties and it appears that it is coming in as a date with a length of 8.

[RW9]

So what you want to format just the date part and use that, or all the datetime?  Date is simple:

data want;
  datepartonly=datepart(yourdatetime);
  format datepartonly mmddyy10.;
run;

If its all the datetime, then you may need to create your own picture format.  Here are the available formats:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a001263753.htm

Out of interest, why can you not use the date as is?  If anything that is fine, and if you need it for any other purpose (filename for instance) then I would highly recommend using ISO dates which can be sorted correctly.

[Dogo23]

Hi RW9,

I would need to refrence the table right? I did it like so (shown below) and I get this error: "ERROR: Invalid variable specification, tableau.metadata_match. Variable names of the form X.X must be either FIRST.X or LAST.X."

data want;
datepartonly=datepart(processed_dttm);
format datepartonly mmddyy10.
from table1;
run;

[Dogo23]

Quick update all,

I made a change and ran in like this 

proc sql; 

select marketing_id

send_date as psend_date format mmddyy8.,

It technically formatted correctly, but now the date show as protected ("*****") in the output and when I click into the cell, it shows every date 12 12/31/9999 instead of the correct date. Any clue as to why?

Thanks

[Reeza · Accepted solution]

---

@schlotty23 wrote:

Quick update all,

I made a change and ran in like this 

proc sql; 

select marketing_id

send_date as psend_date format mmddyy8.,

It technically formatted correctly, but now the date show as protected ("*****") in the output and when I click into the cell, it shows every date 12 12/31/9999 instead of the correct date. Any clue as to why?

Thanks

---

You have a datetime not a date. To get the date portion only use DATEPART() function or a datetime format. Your also missing the = sign on the FORMAT. 

Please note that SAS has two variable types, numeric and character. Date is a numeric variable. 

proc sql;
select marketing_id
Datepart(send_date) as psend_date format =mmddyy8. ,

or 

proc sql;
select marketing_id
send_date as psend_date format=dtdate9. 

[Dogo23]

Thanks Reeza, this worked!

[ballardw]

Note that Proc Format will allow you do some pretty creative custom formatting of dates and datetimes using directives.

So you could create your own custom format, may be name it dtmmddyy to use showing only the date portion as desired without having to go the datepart route.

[Dogo23]

Also good to know. Thanks!

[ballardw]

Here are a couple of examples of custom datetime formats

proc format library=work;
/* default datetime18 shows 2 digit year (with 2 leading blanks, this makes custom
   datetime format that uses 18 digits and 4 digit year
   characters not part of a directive such as : or / are displayed as literal character
*/
picture mydatetime ( default=18)
low-high = '%d%b%Y:%H:%M:%S'   (datatype=datetime)
;
/*to display a datetime as first day of the month in mm/dd/yyyy appearance*/
picture dtfixed (default=10)
low-high = '%0m/01/%Y'  (datatype=datetime)
;
run;

data _null_;
   x='15Feb2017:10:12:13'dt;
   put x= mydatetime. x= datetime18. x= dtfixed.;
run;

Look in the Proc Format Picture statement for meanings of the directives. Do not put the target value in side double quotes such as

"%0m/01/%Y" as then the macro processor will kick in looking for macros named 0m and Y and many errors result.