topic decimals in ods tagsets output in New SAS User

decimals in ods tagsets output

Batman — Thu, 06 Feb 2020 21:13:39 GMT

data a;

For some reason, this code produces two decimals in the xml file. Is there way to round it to an integer?

x=1;

proc sql noprint;
select 1234567890 format=32. into: numval
from a;
%put &numval;

data b;
y=put(&numval,dollar15.);
run;

ods tagsets.excelxp file=[fileref];
proc print data=b;
run;
ods tagsets.excelxp close;

Re: decimals in ods tagsets output

Patrick — Fri, 07 Feb 2020 02:54:06 GMT

If you look into the generated XML you find below:

        <Cell ss:StyleID="data__l1" ss:Index="2"><Data ss:Type="Number">1234567890</Data></Cell>

Excel then just applies the standard format for numbers which adds the two decimals.

Using ods tagsets.excelxp there might be ways to instruct SAS to create a type of Text/String and write the source variable without changing the string.

Alternatively use the more modern ODS EXCEL destination.

data dual;
  x=1;
  output;
  stop;
run;

%let numval=;
proc sql noprint;
  select 1234567890 format=32. into: numval
  from dual;
quit;
%put &=numval;

data b;
  y=put(&numval,dollar15.);
  output;
  stop;
run;

ods excel file='~/test.xlsx';

proc print data=b;
run;

ods excel close;

You can find a related discussion here.

Re: decimals in ods tagsets output

ballardw — Sat, 08 Feb 2020 00:13:32 GMT

@Patrick There is also a data style element that is assigning a currency type.

I spend some time with TAGATTR options and was able to create a type=string but it would get the currency symbol, commas and decimal point stripped out.

I tried: 1) modify the created xml in Excel by deleting the cell

2) entering the dollar formatted value using the Excel apostrophe before entering the value to get a string value

3) save back to XML

At which point I found that there was

Type="String" X:ticked="1"

I was able to get the TAGGATR to have that value BUT the StyleID was still pointing to that currency still and stripping the currency symbols. So there might be something in the tagset that treats Currency appearing values before applying the TAGATTR properties.

</Style>
<Style ss:ID="data__l1" ss:Parent="data__l">
<Protection ss:Protected="1" />
<NumberFormat ss:Format="Currency" />
</Style>

/* and then the value*/
<Cell ss:StyleID="data__l1" ss:Index="2"><Data ss:Type="String" X:ticked="1">1234567890</Data></Cell>

At which point I gave up trying as I wasn't understanding what rules might be involved.

Re: decimals in ods tagsets output

Cynthia_sas — Sat, 08 Feb 2020 00:45:06 GMT

Hi:

Wouldn't be nice if there was a simple style override that allowed you to specify a Microsoft format for numbers? Really nice?

Wait a minute. There is a simple style override that you can use with both TAGSETS.EXCELXP and ODS EXCEL. It is the TAGATTR style override, as shown below:

You can apply the $ format to the original numeric variable, as shown in the code, without needing to make a character variable and you can get rid of the decimal places. All in one simple Microsoft format. No need to reverse engineer the XML at all. I did an explicit right-justify on the character variable and an explicit center justify on the numeric variable. But as you can see, they have the SAME format in the Excel sheet.

... gotta love the ODS developers!

Cynthia