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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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

11 REPLIES 11
Dogo23
Quartz | Level 8

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

TomKari
Onyx | Level 15

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
Quartz | Level 8
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8
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
Quartz | Level 8

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
Super User

@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
Quartz | Level 8

Thanks Reeza, this worked!

ballardw
Super User

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
Quartz | Level 8

Also good to know. Thanks!

ballardw
Super User

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.

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 35695 views
  • 3 likes
  • 5 in conversation