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;
Sheet1
Sheet2
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
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.
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: