Desktop productivity for business analysts and programmers

Format Datetime to mm/dd/yyy

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

Format Datetime to mm/dd/yyy

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!


Accepted Solutions
Solution
‎12-13-2017 11:17 AM
Super User
Posts: 22,850

Re: Format Datetime to mm/dd/yyy


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. 

 

 

View solution in original post


All Replies
Contributor
Posts: 55

Re: Format Datetime to mm/dd/yyy

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

PROC Star
Posts: 1,263

Re: Format Datetime to mm/dd/yyy

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

Contributor
Posts: 55

Re: Format Datetime to mm/dd/yyy

Hi Tom,

I looked at the table properties and it appears that it is coming in as a date with a length of 8.
Super User
Super User
Posts: 9,211

Re: Format Datetime to mm/dd/yyy

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.

Contributor
Posts: 55

Re: Format Datetime to mm/dd/yyy

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;
Contributor
Posts: 55

Re: Format Datetime to mm/dd/yyy

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

 

Solution
‎12-13-2017 11:17 AM
Super User
Posts: 22,850

Re: Format Datetime to mm/dd/yyy


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. 

 

 

Contributor
Posts: 55

Re: Format Datetime to mm/dd/yyy

Thanks Reeza, this worked!

Super User
Posts: 13,046

Re: Format Datetime to mm/dd/yyy

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.

Contributor
Posts: 55

Re: Format Datetime to mm/dd/yyy

Also good to know. Thanks!

Super User
Posts: 13,046

Re: Format Datetime to mm/dd/yyy

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 349 views
  • 1 like
  • 5 in conversation