topic Re: Format Datetime to mm/dd/yyy in SAS Enterprise Guide

Format Datetime to mm/dd/yyy

Dogo23 — Wed, 13 Dec 2017 15:24:56 GMT

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!

Re: Format Datetime to mm/dd/yyy

Dogo23 — Wed, 13 Dec 2017 15:26:29 GMT

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

Re: Format Datetime to mm/dd/yyy

TomKari — Wed, 13 Dec 2017 15:30:43 GMT

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

Re: Format Datetime to mm/dd/yyy

RW9 — Wed, 13 Dec 2017 15:34:33 GMT

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.

Re: Format Datetime to mm/dd/yyy

Dogo23 — Wed, 13 Dec 2017 15:35:43 GMT

Hi Tom,

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

Re: Format Datetime to mm/dd/yyy

Dogo23 — Wed, 13 Dec 2017 15:39:15 GMT

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;

Re: Format Datetime to mm/dd/yyy

Dogo23 — Wed, 13 Dec 2017 15:43:42 GMT

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

Re: Format Datetime to mm/dd/yyy

Reeza — Wed, 13 Dec 2017 15:54:53 GMT

@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. ,

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

Re: Format Datetime to mm/dd/yyy

Dogo23 — Wed, 13 Dec 2017 16:18:57 GMT

Thanks Reeza, this worked!

Re: Format Datetime to mm/dd/yyy

ballardw — Wed, 13 Dec 2017 17:54:55 GMT

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.

Re: Format Datetime to mm/dd/yyy

Dogo23 — Thu, 14 Dec 2017 21:59:17 GMT

Also good to know. Thanks!

Re: Format Datetime to mm/dd/yyy

ballardw — Fri, 15 Dec 2017 20:50:36 GMT

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.