Picture Format not working in ODS EXCEL

[PaigeMiller]

proc format;
	picture million(round) low-high='000,000,000,009.9MM' (prefix='$' mult=.00001);
run;
data a;
    balance=333333.33; output;
    balance=725000000.00; output;
run;
ods listing;
proc report data=a;
    columns balance;
    define balance/display format=million.;
run;
ods listing close;

This produces the proper output using the Picture Format

                  balance
                   $0.3MM
                 $725.0MM

But if I use ODS EXCEL, the picture format isn't used and produces an incorrect result

ods excel file="temp1.xlsx";
proc report data=a;
    columns balance;
    define balance/display format=million.;
run;
ods excel close;

Why does this happen? How do I fix it?

[Cynthia_sas]

Hi: I think you're going to have to use TAGATTR to send a Microsoft format to Excel. I'm not in a place to run code right now. A quicker alternative may be to use your PICTURE format to make a character variable and then use the character variable in PROC REPORT and just right justify the value with a STYLE override.
Cynthia

[ballardw · Accepted solution]

I tried an odd thought because of the Excel "enter a ' for literal values" behavior with Prefix="'$" , adding a ' to the prefix. The values appear as desired EXCEPT the ' appeared.

So I tried a NULL character, the ASCII 255 in the prefix before the $ and the appearance is as desired but Excel doesn't treat the values as numeric for use in functions.

Update: This topic/solution presents a workaround for the problem.

[PaigeMiller]

Well, @ballardw , I like the cut of your jib (to use a very old archaic expression). I think you have found a valuable workaround, as the Excel file I am creating is designed for reporting purposes, and I don't really care if the results are not numeric (although I may regret thinking so and thus other solutions are still welcomed).

[ballardw]

---

@PaigeMiller wrote:

Well, @ballardw , I like the cut of your jib (to use a very old archaic expression). I think you have found a valuable workaround, as the Excel file I am creating is designed for reporting purposes, and I don't really care if the results are not numeric (although I may regret thinking so and thus other solutions are still welcomed).

---

I appreciate the thought.

As a fairly long-time member of the Society for Creative Anachronism "archaic" is part of the club.

I don't have a lot of interest in trying to work around the Excel cell formatting instructions to do such things.

[Cynthia_sas]

Hi:

  I agree, working around the Excel formatting was going to take too long. But if you're going to make a character variable, then just make a character variable and it seems to work OK with ODS EXCEL, no changes at all needed to the user-defined format:

proc format;
	picture million(round) 
            low-high='000,000,000,009.9MM' 
            (prefix='$' mult=.00001);
run;
  
data a;
    length balchar $22;
	infile datalines;
	input balance;
	ordnum = _n_;
	balchar = put(balance,million.);
	putlog _all_;
return;
datalines;
333333.33 
725000000.00
; 
run;
   
ods html(id=1) path='c:\temp\' file='pic_test.html';
ods excel(id=2) file='c:\temp\pic_test.xlsx';
proc report data=a;
    columns ordnum balance balchar bal2;
    define balance/display format=million. "With Format";
	define balchar/display "Balance from DATA step"
	       style(column)={just=r};
	define bal2/computed "Balance from COMPUTE block"
	       style(column)={just=r};
	compute bal2 / character length=22;
      	bal2 = put(balance,million.);
	endcomp;
run;
ods html(id=1) close;
ods excel(id=2) close;

BALCHAR is made using the user-defined format in the original DATA step and BAL2 is computed in PROC REPORT using the original format. Both of these approaches work for me:

Created HTML output for comparison. Of course, in the HTML, the PICTURE format works very nicely.

Hope this helps,

Cynthia

[PaigeMiller]

Thanks, @Cynthia_sas . Right now, I will stick with the solution from @ballardw which requires me to add one more character into my existing program.

However, I still have this feeling that even though the report I am producing is just that, a static report, someone is going to try to add the loan balances in Excel, and as all of these are really just text strings, they can't be added, and then I'm going to need to change the report to have numerics there. So I will still be happy to have a solution that is numeric.