Proc Report Date Format

[KevinC_]

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!

[dcruik]

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]

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_]

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.

[portiaconant]

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;