proc export date formats

[TimCampbell]

Hi everyone,

I have some code that is using proc export to output two datasets as separate sheets in an excel workbook.

The recipient of the file has asked me to change the format of the dates in the spreadsheet but this is turning out not to be as simple as I thought.

In the example code below I create a dates table with 3 columns a date formatted as date9, a DDMMYY10. (what our end user wants) and then a character version just for completeness.

when the data ends up in the spreadsheet however...

on 'MySheet1' the outdate columns is formatted with hyphens (date11. not date9.) and the fmtdate doesn't have the leading zero for the day.

then on 'MySheet2' the dates for fmtdate are also formatted with hyphens.

So I end up with nothing formatted as date9. and the actual date format I want only working when created as text.

Does anyone have any idea what is going on?

data dates;
	format outdate date9. fmtdate DDMMYY10. chardate $10.;

	do outdate='01jan2019'd to '31jan2019'd;
		fmtdate=outdate;
		chardate=put(outdate,DDMMYY10.);
		output;
	end;
run;

proc export
	data = dates
	dbms = xlsx
	outfile = "/pathtofile/TestExcel.xlsx"
	replace;
	sheet="MySheet1";
run;
proc export
	data = dates
	dbms = xlsx
	outfile = "/pathtofile/TestExcel.xlsx"
	replace;
	sheet="MySheet2";
run;

Sheet1Sheet2

We have a workaround using ods excel and proc print (below) to create the spreadsheet which works fine (apart from the date9 format and that the 'style=' option seems to be ignored) so it isn't business critical to get this resolved but it would be useful to know what I am doing wrong.

ods excel file = "/pathtofile/TestExcel2.xlsx";
ods excel options(sheet_name="MySheet1");
proc print data=dates noobs style=minimal;
run;

ods excel options(sheet_name="MySheet2");
proc print data=dates noobs style=minimal;
run;

ods excel

Thanks,

Tim

[ErikLund_Jensen]

Hi @TimCampbell 

I have had similar problems, including leading zeroes in numbers, and has lived without a solution until ODS Excel was introduced. So I don't think you have done anything wrong. It seems to be two different engines with ODS excel giving you better control over the way Excel presents the output. 

Morale: SAS and Excel were not born under the same sun, and they should never meet....

Best

Erik

[Tom · Accepted solution]

If you want Excel to display dates in a particular style you need to tell Excel that.  I don't think PROC EXPORT (or the XLSX libname engine) actually tells Excel anything specific and so it just defaults to how your installation of EXCEL likes to display dates.

ODS EXCEL does a better job of guessing what style to tell EXCEL to use. And it allows you to use the TAGATTR style attribute to override its defaults.

Please read the responses on this other question 

[TimCampbell]

Thanks Tom,

Searching for TAGATTR gave me all the answers I needed.

For future reference this page below helped a lot, also showed me where to put the style=minimal option to get it to work properly.

http://support.sas.com/resources/papers/proceedings10/031-2010.pdf

And here is the final version of the code that works for me.

data dates;
	format outdate date9. fmtdate DDMMYY10. chardate $10.;

	do outdate='01jan2019'd to '31jan2019'd;
		fmtdate=outdate;
		chardate=put(outdate,DDMMYY10.);
		output;
	end;
run;

ods excel file = "/pathtofile/TestExcel2.xlsx" style=minimal;
ods excel options(sheet_name="MySheet1");
proc print data=dates noobs;
	var outdate fmtdate / style=[tagattr='format:dd/mm/yyyy'];
	var chardate;
run;

Tim