Hi,
I have the below code based on 38143 - Apply date formats with the ExcelXP destination:
ods noresults;
ods _all_ close;
ods tagsets.excelxp file='c:\temp\temp.xml' style=analysis;
/* create a new DateTime column from the date column */
data buy;
date=date();
datetime=datetime();
* dates must be converted to datetime format ;
datetime2=dhms(date,0,0,0);
format date date9. datetime datetime2 datetime21.;
run;
/* Pre-9.2, the format/informat name was IS8601DT . That name is still */
/* allowed in 9.2, although we are */
/* now using the name E8601DT (E=Extended vs. B=Basic). */
/* set the format for the DateTime coming from SAS */
/* and set the Excel type and format so Excel knows what to do with it */
proc print data=buy;
format datetime datetime2 e8601dt.;
var date;
var datetime;
var datetime2;
var datetime / style(data)={tagattr='type:DateTime format:yyyy-mm-dd'};
var datetime / style(data)={tagattr='type:DateTime format:mm-dd-yy'};
var datetime / style(data)={tagattr='type:DateTime format:yyyy-mm-dd:hh:mm:ss'};
var datetime / style(data)={tagattr='type:DateTime format:dd/mm/yyyy'}; * <<< not working in excel ;
var datetime2 / style(data)={tagattr='type:DateTime format:yyyy-mm-dd'};
var datetime2 / style(data)={tagattr='type:DateTime format:mm-dd-yy'};
var datetime2 / style(data)={tagattr='type:DateTime format:yyyy-mm-dd:hh:mm:ss'};
var datetime2 / style(data)={tagattr='type:DateTime format:dd/mm/yyyy'}; * <<< not working in excel ;
run;
ods tagsets.excelxp close;
ods listing;
It "works" except for the dd/mm/yyyy format.
I think the issue is due to Excel being poorly designed (sorry, I'm not a fan of Excel): http://excel.tips.net/T003096_Adding_a_Custom_Format_to_those_Offered_by_Excel.html
I've tried creating the custom format in an autostart template, but that's not working, I think because the output is XML - it does work (the custom format is in place) if I just launch Excel without opening a saved workbook.
Any bright ideas how I can 1) save the cell/column as a proper Excel date (i.e. not as '25/12/2015 string), and 2) have Excel format that as dd/mm/yyyy?
BTW it's Excel 2007 with English (Australia) regional format. Changing the region settings on the end users computer is not an option.
Thanks,
Scott
Hi All,
Thanks all for your responses.
, your comment "Usual protection in most apps is quoting or some form of "escaping" " inspired me to try the below, which works in my environment (Excel 2007, Australian Regional Settings, etc.). YMMV.
/* create a new DateTime column from the date column */
data buy;
date=date();
datetime=datetime();
* dates must be converted to datetime format ;
datetime2=dhms(date,0,0,0);
format date date9. datetime datetime2 datetime21.;
run;
/* Pre-9.2, the format/informat name was IS8601DT . That name is still */
/* allowed in 9.2, although we are */
/* now using the name E8601DT (E=Extended vs. B=Basic). */
/* set the format for the DateTime coming from SAS */
/* and set the Excel type and format so Excel knows what to do with it */
ods noresults;
ods _all_ close;
ods tagsets.excelxp file='c:\temp\temp.xml' style=analysis;
proc print data=buy;
format datetime datetime2 e8601dt.;
var date;
var datetime;
var datetime2;
var datetime2 / style(data)={tagattr='type:DateTime format:dd.mm.yyyy'}; * works ;
var datetime2 / style(data)={tagattr='type:DateTime format:ddd-dd.mm.yyyy'}; * works ;
var datetime2 / style(data)={tagattr='type:DateTime format:ddd-dd/mm/yyyy'}; * does not work ;
var datetime2 / style(data)={tagattr='type:DateTime format:dd/mm/yyyy'}; * does not work ;
var datetime2 / style(data)={tagattr='type:DateTime format:dd\/mm\/yyyy'}; * works, go figure ;
run;
ods tagsets.excelxp close;
ods listing;
Note the "escaped" slashes in the last example.
I've tried to attach a screenshot of the results in my environment:
* Advanced Editor, Attach Files (click Choose file button): Max Size: 1.0K, no file type allowed.
* Insert Image (click the camera icon): Just times out when I try to insert a 97Kb png file into the message
If someone can tell me how to attach screenshots to a message I'll edit the message later.
Regards,
Scott
Change format: dd/mm/yyyy to ddmmyys10.?.
as / only appears in that tagattr format, it might be a special character to either SAS or to excel. Usual protection in most apps is quoting or some form of "escaping"
For confirmation replace the / with a -
oddly I found I need no extra quoting,
and the dd/mm/yyyy formats appear correctly:
either because
dd/mm/yyyy is accepted default UK date formatting
or
my tagsets.excelxp is on a different release
tagsets.excelxp v1.130, 08/02/2013
SAS9.4 TS1M2
64bit win7
excel 2007
I would think the format specification i.e. {tagattr='type:DateTime format:dd/mm/yyyy'} should match the format as listed in Excel, i.e. Home -> Number format dropdown -> Custom -> dd/mm/yyyy.
As explained in the link above Adding a Custom Format to those Offered by Excel (Microsoft Excel), the issue is due to the fact that dd/mm/yyyy is not a date format shipped with Excel.
I was hoping someone here would have a bright idea I haven't been able to think of.
I do quite a bit of custom formatting in excel
any combination of features seem to work, so it is easy to get
Sat-04/07/2015 |
with custom format
ddd-dd/mm/yyyy
either in the tagattr
or in excel
Scott
if you still can't get the tagattr to work, try the ODS and base reporting community at https://communities.sas.com/community/support-communities/ods_and_base_reporting
good luck
Peter
Another way of thinking about this is to create and empty Excel which is setup as you would like the final output to look like - e.g. formats etc. Then export your data from SAS into a CSV file. Then its simply a matter of having the template file load that into itself.
Your program works for me using PC-SAS 9.4 and Excel 2010.
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013)
When I ask Excel to make a custom format that looks like that it uses
dd/mm/yyyy;@
Try adding the semicolon and at sign.
Thank you!!
It seems indicating you are defining a custom format (with ;@) works with any format Excel accepts.
All of these worked (with MS Excel, Spanish but probably with all of them when using ;@ and indicating the locale/language as we'll see next).
As said, the trick is to save as "Excel 2003 XML", open it as text (Notepad) and search for the definition like <NumberFormat ss:Format="dd\-mm\-yyyy;@"/>.
var datetime1 datetime2 / style(data) = {tagattr='type:DateTime format:dd/mm/yyyy;@'};
var datetime1 datetime2 / style(data) = {tagattr='type:DateTime format:dd-mm-yyyy;@'};
/* In XML appears as: <NumberFormat ss:Format="dd\-mm\-yyyy;@"/>
so it seems escaping is not necessary when adding the custom format indicator ;@
but as we'll see one should substitute ' with " (HTML) */
And these ones depending on locale/language work too:
- This one will show the month in the default Excel language, "16-jun-2016":
var datetime1 datetime2 / style(data) = {tagattr='type:DateTime format:dd-mmm-yyyy;@'};
- This one in International Spanish, "16 de Junio de 2016":
var datetime1 datetime2 / style(data) = {tagattr='type:DateTime format:[$-C0A]d\ "de"\ mmmm\ "de"\ yyyy;@'};
- This in English (USA), "16-jun-2016":
var datetime1 datetime2 / style(data) = {tagattr='type:DateTime format:[ENG][$-409]d\-mmm\-yy;@'};
Hi All,
Thanks all for your responses.
, your comment "Usual protection in most apps is quoting or some form of "escaping" " inspired me to try the below, which works in my environment (Excel 2007, Australian Regional Settings, etc.). YMMV.
/* create a new DateTime column from the date column */
data buy;
date=date();
datetime=datetime();
* dates must be converted to datetime format ;
datetime2=dhms(date,0,0,0);
format date date9. datetime datetime2 datetime21.;
run;
/* Pre-9.2, the format/informat name was IS8601DT . That name is still */
/* allowed in 9.2, although we are */
/* now using the name E8601DT (E=Extended vs. B=Basic). */
/* set the format for the DateTime coming from SAS */
/* and set the Excel type and format so Excel knows what to do with it */
ods noresults;
ods _all_ close;
ods tagsets.excelxp file='c:\temp\temp.xml' style=analysis;
proc print data=buy;
format datetime datetime2 e8601dt.;
var date;
var datetime;
var datetime2;
var datetime2 / style(data)={tagattr='type:DateTime format:dd.mm.yyyy'}; * works ;
var datetime2 / style(data)={tagattr='type:DateTime format:ddd-dd.mm.yyyy'}; * works ;
var datetime2 / style(data)={tagattr='type:DateTime format:ddd-dd/mm/yyyy'}; * does not work ;
var datetime2 / style(data)={tagattr='type:DateTime format:dd/mm/yyyy'}; * does not work ;
var datetime2 / style(data)={tagattr='type:DateTime format:dd\/mm\/yyyy'}; * works, go figure ;
run;
ods tagsets.excelxp close;
ods listing;
Note the "escaped" slashes in the last example.
I've tried to attach a screenshot of the results in my environment:
* Advanced Editor, Attach Files (click Choose file button): Max Size: 1.0K, no file type allowed.
* Insert Image (click the camera icon): Just times out when I try to insert a 97Kb png file into the message
If someone can tell me how to attach screenshots to a message I'll edit the message later.
Regards,
Scott
Thank you, ScottBass.
As Tom said, any format which appears in the Excel 2003 XML seems to work adding ;@ in the end of the format to indicate Excel one is defining a custom format, and it seems escaping with \ is never needed that way.
I posted some examples, and even choosing the locale/language works.
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!