Why does ODS EXCEL Produce ##### ?

[PaigeMiller]

data WORK.b;
  infile datalines truncover;
  input fico xyz rate1:9.5 rate2:9.5;
  format rate1 rate2 9.5;
datalines;
. . 0.06834 0.00791
. 180 -0.05625 0.02813
. 190 0.02119 0.02364
. 200 0.15692 0.02431
. 210 0.10813 0.01244
. 220 0.09780 0.00986
. 230 0.08535 0.00711
. 240 0.06567 0.00501
. 250 0.04516 0.00331
. 260 0.01978 0.00178
. 270 -0.00247 0.00113
. 280 -0.01988 0.00062
. 290 -0.04668 0.00028
. 300 -0.05203 0.00006
. 310 -0.06733 0.00003
620 . 0.25154 0.01699
640 . 0.15324 0.02058
660 . 0.12874 0.02002
680 . 0.16208 0.01123
700 . 0.09059 0.01001
720 . 0.06867 0.00918
740 . 0.05724 0.00846
760 . 0.05320 0.00473
780 . 0.03761 0.00376
800 . 0.02477 0.00314
820 . 0.00925 0.00270
840 . 0.00301 0.00237
860 . -0.00502 0.00197
880 . -0.00822 0.00179
900 . -0.01944 0.00190
620 210 0.28471 0.02059
620 220 0.14219 0.00612
620 230 0.14440 0.00492
620 240 0.14601 0.00405
620 250 0.14613 0.00320
620 260 0.13832 0.00297
620 270 0.12852 0.00019
640 200 0.16105 0.02846
640 210 0.12621 0.01564
640 220 0.13876 0.00976
640 230 0.14738 0.00753
640 240 0.14832 0.00542
640 250 0.16813 0.00242
640 260 0.19594 0.00134
640 270 0.22984 0.00160
640 280 0.16159 0.00000
640 290 0.10647 0.00080
660 200 0.12398 0.02925
660 210 0.07233 0.01044
660 220 0.14724 0.01032
660 230 0.14187 0.00747
660 240 0.13069 0.00600
660 250 0.12587 0.00412
660 260 0.13015 0.00314
660 270 0.13231 0.00086
660 280 0.16766 0.00001
680 200 0.29790 0.02081
680 210 0.14322 0.01106
680 220 0.13486 0.01118
680 230 0.12976 0.00817
680 240 0.12767 0.00590
680 250 0.11575 0.00423
680 260 0.09714 0.00199
680 270 0.09336 0.00137
680 280 0.08781 0.00065
680 290 0.11904 0.00000
700 190 0.04182 0.02352
700 200 -0.01940 0.01273
700 210 0.09315 0.01144
700 220 0.11034 0.01040
700 230 0.10379 0.00802
700 240 0.08766 0.00575
700 250 0.08203 0.00399
700 260 0.04734 0.00251
700 270 0.07937 0.00155
700 280 0.08138 0.00049
700 290 0.14399 0.00222
720 190 -0.02153 0.02695
720 200 -0.01507 0.00859
720 210 0.07237 0.01276
720 220 0.08895 0.01019
720 230 0.08323 0.00760
720 240 0.07072 0.00597
720 250 0.06456 0.00413
720 260 0.04925 0.00223
720 270 0.04954 0.00197
720 280 0.03344 0.00079
720 290 0.03510 0.00063
720 300 -0.04247 0.00000
720 310 -0.03843 0.00005
740 180 -0.13637 0.03546
740 190 0.03265 0.00583
740 200 0.04086 0.01227
740 210 0.05455 0.01284
740 220 0.05917 0.01197
740 230 0.06541 0.00934
740 240 0.06502 0.00631
740 250 0.05180 0.00448
740 260 0.05043 0.00266
740 270 0.03396 0.00193
;;;;

ods escapechar='^';
ods excel file="temp1.xlsx";
proc report data=b;
	columns fico xyz,rate2;
	define fico/group "FICO";
	define rate2/sum format=8.2 "NCO^{newline 1}Rate" style(column)={tagattr='format:####0.00%'};
	define xyz/across "XYZ";
run;
ods excel close;

This produces an Excel spreadsheet that contains ##### in every cell that does not have a missing value, and so the numbers are not readable. Why? How can I prevent this?

Note that if I change the label "NCO^{newline 1}Rate" to "NCO Rate", the numbers become readable, but that's not what I want. I want "NCO^{newline 1}Rate" as shown, with readable numbers. It seems that Excel doesn't use the width of the numbers in the cell to determine the width of the  column, it just uses the width of the label. Is that right?

[VDD]

there should be a column width option you can set.

[PaigeMiller]

---

@VDD wrote:

there should be a column width option you can set.

---

Yes, I am aware of that as well, and that does work, but I'd prefer not to, if there is some way to make Excel "auto-adjust the width" without me having to specify a width for each of the 15 or so columns. I run into this problem occasionally with ODS EXCEL, it's not just this one table that is causing the problem, and I don't want to have to hard code columns widths every time I run into this problem if there's a way to handle it automatically.

[ballardw · Accepted solution]

When I removed the "format=8.2" for the rate2 the numbers display properly, i.e. not ######

I'm going to suspect something discombobulated between the specifed SAS format, other than best. perhaps, and the taggattr.

	XYZ
	180	190	200	210	220	230	240	250	260	270	280	290	300	310
FICO	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate	NCO Rate
620	.	.	.	2.06%	0.61%	0.49%	0.41%	0.32%	0.30%	0.02%	.	.	.	.
640	.	.	2.85%	1.56%	0.98%	0.75%	0.54%	0.24%	0.13%	0.16%	0.00%	0.08%	.	.
660	.	.	2.93%	1.04%	1.03%	0.75%	0.60%	0.41%	0.31%	0.09%	0.00%	.	.	.
680	.	.	2.08%	1.11%	1.12%	0.82%	0.59%	0.42%	0.20%	0.14%	0.07%	0.00%	.	.
700	.	2.35%	1.27%	1.14%	1.04%	0.80%	0.58%	0.40%	0.25%	0.16%	0.05%	0.22%	.	.
720	.	2.70%	0.86%	1.28%	1.02%	0.76%	0.60%	0.41%	0.22%	0.20%	0.08%	0.06%	0.00%	0.01%
740	3.55%	0.58%	1.23%	1.28%	1.20%	0.93%	0.63%	0.45%	0.27%	0.19%	.	.	.	.

[PaigeMiller]

Well that certainly solved the problem, although I wish I understood why it works.

Extra credit for using the word discombobulated here in the SAS Communities. I believe you get some sort of prize.

[ballardw]

---

@PaigeMiller wrote:

Well that certainly solved the problem, although I wish I understood why it works.

Extra credit for using the word discombobulated here in the SAS Communities. I believe you get some sort of prize.

---

My initial "guess", and why I tried playing with the SAS format at all, was that the F8. part was asking for more actual characters than the Excel 0.00%. So I commented out the format and got the "desired" result. I played with some other formats fewer digits, no decimals, including a custom format I have to create "percent" type values, but all of those except BEST. had what ever conflict or translation issue you encountered.