SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Format Excel date as dd/mm/yyyy

Accepted Solution Solved
Reply
Super Contributor
Posts: 392
Accepted Solution

Format Excel date as dd/mm/yyyy

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='typeSmiley Very HappyateTime format:yyyy-mm-dd'};

var datetime  / style(data)={tagattr='typeSmiley Very HappyateTime format:mm-dd-yy'};

var datetime  / style(data)={tagattr='typeSmiley Very HappyateTime format:yyyy-mm-dd:hh:mm:ss'};

var datetime  / style(data)={tagattr='typeSmiley Very HappyateTime format:dd/mm/yyyy'}; * <<< not working in excel ;

var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:yyyy-mm-dd'};

var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:mm-dd-yy'};

var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:yyyy-mm-dd:hh:mm:ss'};

var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime 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


Accepted Solutions
Solution
‎07-08-2015 12:48 AM
Super Contributor
Posts: 392

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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='typeSmiley Very HappyateTime format:dd.mm.yyyy'};      * works ;

  var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:ddd-dd.mm.yyyy'};  * works ;

  var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:ddd-dd/mm/yyyy'};  * does not work ;

  var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:dd/mm/yyyy'};      * does not work ;

  var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime 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

View solution in original post


All Replies
Occasional Contributor
Posts: 12

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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

Valued Guide
Posts: 2,188

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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 -

Valued Guide
Posts: 2,188

Re: Format Excel date as dd/mm/yyyy

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

Super Contributor
Posts: 392

Re: Format Excel date as dd/mm/yyyy

I would think the format specification i.e. {tagattr='typeSmiley Very HappyateTime 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.

Valued Guide
Posts: 2,188

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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

Valued Guide
Posts: 2,188

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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

Super User
Super User
Posts: 8,605

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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. 

Super User
Super User
Posts: 7,375

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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.

New Contributor
Posts: 3

Re: Format Excel date as dd/mm/yyyy

[ Edited ]

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='typeSmiley Very HappyateTime format:dd/mm/yyyy;@'};

 

var datetime1 datetime2 / style(data) = {tagattr='typeSmiley Very HappyateTime 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='typeSmiley Very HappyateTime format:dd-mmm-yyyy;@'};

 

- This one in International Spanish, "16 de Junio de 2016":

var datetime1 datetime2 / style(data) = {tagattr='typeSmiley Very HappyateTime format:[$-C0A]d\ &quot;de&quot;\ mmmm\ &quot;de&quot;\ yyyy;@'};

 

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

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

 

 

Solution
‎07-08-2015 12:48 AM
Super Contributor
Posts: 392

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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='typeSmiley Very HappyateTime format:dd.mm.yyyy'};      * works ;

  var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:ddd-dd.mm.yyyy'};  * works ;

  var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:ddd-dd/mm/yyyy'};  * does not work ;

  var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime format:dd/mm/yyyy'};      * does not work ;

  var datetime2 / style(data)={tagattr='typeSmiley Very HappyateTime 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

New Contributor
Posts: 3

Re: Format Excel date as dd/mm/yyyy

Posted in reply to ScottBass

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 5670 views
  • 6 likes
  • 6 in conversation