topic Re: Format Excel date as dd/mm/yyyy in Microsoft Integration with SAS

Format Excel date as dd/mm/yyyy

ScottBass — Fri, 03 Jul 2015 04:45:26 GMT

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

Re: Format Excel date as dd/mm/yyyy

gcleggs — Sat, 04 Jul 2015 06:51:06 GMT

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

Re: Format Excel date as dd/mm/yyyy

Peter_C — Sat, 04 Jul 2015 07:33:02 GMT

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 -

Re: Format Excel date as dd/mm/yyyy

Peter_C — Sat, 04 Jul 2015 08:18:50 GMT

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

my tagsets.excelxp is on a different release

tagsets.excelxp v1.130, 08/02/2013

SAS9.4 TS1M2

64bit win7

excel 2007

Re: Format Excel date as dd/mm/yyyy

ScottBass — Sat, 04 Jul 2015 08:24:45 GMT

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.

Re: Format Excel date as dd/mm/yyyy

Peter_C — Sat, 04 Jul 2015 08:58:50 GMT

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

Re: Format Excel date as dd/mm/yyyy

Peter_C — Sat, 04 Jul 2015 09:05:36 GMT

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

Re: Format Excel date as dd/mm/yyyy

RW9 — Sat, 04 Jul 2015 12:53:05 GMT

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.

Re: Format Excel date as dd/mm/yyyy

Tom — Sat, 04 Jul 2015 13:15:59 GMT

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.

Re: Format Excel date as dd/mm/yyyy

ScottBass — Wed, 08 Jul 2015 04:48:39 GMT

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

Re: Format Excel date as dd/mm/yyyy

rmoreno — Thu, 16 Jun 2016 16:59:36 GMT

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;@'};

Re: Format Excel date as dd/mm/yyyy

rmoreno — Thu, 16 Jun 2016 16:56:05 GMT

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.