Format Excel date as dd/mm/yyyy

[ScottBass]

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

[gcleggs]

Change format: dd/mm/yyyy to ddmmyys10.?.

[Peter_C]

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 -

[Peter_C]

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

[ScottBass]

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.

[Peter_C]

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

[Peter_C]

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

[RW9]

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. 

[Tom]

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.

[rmoreno]

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 &quot; (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\ &quot;de&quot;\ mmmm\ &quot;de&quot;\ yyyy;@'};

- This in English (USA), "16-jun-2016":

var datetime1 datetime2 / style(data) = {tagattr='type:DateTime format:[ENG][$-409]d\-mmm\-yy;@'};

[ScottBass · Accepted solution]

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

[rmoreno]

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.