The SAS Output Delivery System and reporting techniques

[ods tagsets.excelxp] Modify the XML data type

Reply
Contributor
Posts: 36

[ods tagsets.excelxp] Modify the XML data type

Hello,
I use ods tagsets.excelxp to make xml files.

The following macro works well for integer data.

[pre]%macro pctCol(MyTable, MyVar1, MyVar2);
title2 &MyLayout2 "Pourcentages (%)";
proc tabulate data=&MyTable;
class &MyVar1 &MyVar2;
classlev &MyVar1 &MyVar2;
tables &MyVar1 all='TOTAL',&MyVar2 * colpctn='';
run;
%mend;[/pre]

But the decimal data appear with a point as decimal separator and with XML type

[pre]DATA ss:Type="String">17.69[/pre] instead of [pre]DATA ss:Type="Number">17,89[/pre] so that Excel considers the cells with decimal data as strings.

I have tried formats such as NUMx10.2, the commas appears in the cells but the XML type is still String and Excel considers the cells as strings.

I have also tried the tagattr option within the proc tabulate :

[pre]classlev &MyVar1 &MyVar2 / style={tagattr='format:##0,0'};[/pre] but the decimal separator remains a point.

I have looked for an option telling SAS to use a comma as decimal separator; I have not found.

If someone has an idea it would help me.

Thank you.
Contributor
Posts: 36

Re: [ods tagsets.excelxp] Modify the XML data type

I have just found a solution that works :

[pre]%macro pctCol(MyTable, MyVar1, MyVar2);
title2 &MyLayout2 "Pourcentages (%)";
proc tabulate DATA=&MyTable f=NUMx10.2 style={tagattr='type:Number Format:###0.0'};
class &MyVar1 &MyVar2;
classlev &MyVar1 &MyVar2 ;
TABLES &MyVar1 ALL='TOTAL',&MyVar2 * colpctn='' / misstext="";
run;
%mend;[/pre]

Thanks. Message was edited by: phil27
Ask a Question
Discussion stats
  • 1 reply
  • 380 views
  • 0 likes
  • 1 in conversation