BookmarkSubscribeRSS Feed
KevinC_
Fluorite | Level 6

Hello Everyone,

I am trying to display the datepart of a date column in proc report and format it as mmddyy10..  The column currently has the hour minute second:

30MAR2015:00:00:00

My code looks like this:

Proc Report data = infile;

define cut_id / format = $10.;

define sale_date /  format = mmddyyd10.;

run;

When I run this code the sale_date column looks like this in Excel:

**********

How do I have the sale_date column formatted as mmddyy10?

Thank you very much for any input you may have!

4 REPLIES 4
dcruik
Lapis Lazuli | Level 10

I would recommend having a separate DATA step before your REPORT procedure that creates a new variable using the datepart() funciton.  Example code below, hope this helps!

data infile_new;

set infile;

saledate=datepart(sale_date);

proc report data=infile_new;

define cut_id / format=$10.;

define saledate / format=mmddyy10.;

run;

Cynthia_sas
SAS Super FREQ

Hi:

  But another issue is HOW is the output being sent to Excel. Sometimes, Excel has its own way of displaying dates, leading zeroes, etc. So the format you WANT, isn't always the format you GET in Excel because Excel does its own thing. This frequently happens when you are using ODS HTML or ODS MSOFFICE2K at the output destination to create output for Excel. With ODS TAGSETS.EXCELXP and SAS 9.4, I don't notice as many issues with Excel and dates but sometimes I have issues with Excel and column widths or decimals or leading zeroes.

cynthia

KevinC_
Fluorite | Level 6

Thank you dcruik and Cynthia.

Cynthia,

The sas runs on unix and uses tagsets.excelxp.  It's supposed to be an automated process without manual intervention.  I went ahead with dcruik's suggestion to change the column format in an earlier dataset.  It seems to have worked.

Thank you both for your input. Smiley Happy

portiaconant
Calcite | Level 5

I had a similar question. However, this is how I changed the date format within the proc report code instead of in a separate data step. You can create an alias variable for your date variable in the column statement ("date"), and then use the compute function to datepart within the proc report program.

 

Proc Report data = infile;

column cut_id sale_date date;

define cut_id /format = $10.;

define sale_date/display noprint;

define date/computed format = mmddyyd10.;

compute date;

   date = datepart(sale_date);

endcomp;

run;

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 4809 views
  • 0 likes
  • 4 in conversation