topic Re: From SAS to Excel - keeping number type while formatting in ODS and Base Reporting

From SAS to Excel - keeping number type while formatting

Multipla99 — Tue, 22 Nov 2022 15:36:07 GMT

ODS Excel and Proc Report is a powerful combination that let us enjoy also our SAS formattade values in Excel. However, I still wonder if there is a way to preserve the data type numeric as number when I send the formatted data from SAS to Excel.

For illustration please see code below. In the code the Swedish locale is used to allow for national formatting, NLNUM12.2, with decimal comma and space as thousand delimiter. The result looks wonderful in Excel. However, the values in the column Invoice are not numbers and can't be used for calculations.

Can I send formatted values from SAS to Excel and also tell Excel to interpret them as numbers? (In the Swedish version of Excel it's possible to format values with decimal comma and space as thousand delimiter, while at the same time maintaining the data type number.)

options locale= sv_se;
goptions device= png;
ods excel                                                
file= "C:\Temp\Cars.xlsx"
; 
proc report
	data= sashelp.cars
	;
	column origin invoice
	; 
	define origin 							
	/ group									
	;
	define invoice 									
	/	analysis 
		format= NLNUM12.2
	;
run
;
ods excel close;        
goptions reset= goptions;

Re: From SAS to Excel - keeping number type while formatting

Shmuel — Tue, 22 Nov 2022 18:36:43 GMT

Can you post what sas format has Invoice variable?

Within SAS, assuming the format is comma12.2, when Invoice is displayed is it like 3.571.144,00 (european option) ?

It seems as if commas are diaplayed as blank space ?!

Re: From SAS to Excel - keeping number type while formatting

Cynthia_sas — Tue, 22 Nov 2022 18:40:57 GMT

Hi:
I believe you may need to use a STYLE override in PROC REPORT that changes the TAGATTR style attribute for TYPE. Here's the documentation page for ODS EXCEL https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm#p1hoqu3lkmdf6wn1stuwbyt7yyua and if you look at Examples 2 and 3, you'll see how to use TAGATTR with TYPE:number and see whether that works for you.
Cynthia

Re: From SAS to Excel - keeping number type while formatting

Ksharp — Wed, 23 Nov 2022 02:47:01 GMT

Yes. As Cynthia said you need style TAGATTR= .

https://blogs.sas.com/content/sgf/2022/10/21/formatting-your-microsoft-excel-output-using-ods-excel/

Re: From SAS to Excel - keeping number type while formatting

Tom — Wed, 23 Nov 2022 03:20:25 GMT

The LOCALE setting will control how NLNUM format displays the values. Which is great for a PDF or simple text output.

But for Excel you need to tell Excel how IT should display the values. Use the /style option and TAGATTR setting. So this format string tells Excel to use two decimal places and insert thousand separators.

style(data)={tagattr="format:#,##0.00"}

Now Excel will display two decimal places and thousand separator. It should use the normal characters for those options that the computer that is viewing the XLSX file uses even though the custom formatting instruction is using comma and period. So if you are viewing the file in Sweden then it should do what you want.

If for some reason it is still not display properly in Excel you can use the Options -> Advanced editting options and un-check the "Use system separators" selection you can then fill in the decimal and thousands separator character in the two text boxes there. I don't know how to override the default setting via code from SAS however.

Note that if you are NOT also sending that PROC REPORT output to some other open ODS output destination there is probably no need to attach the NLNUM format to the variable. That just controls how SAS formats the string it uses to pass the number to Excel. And Excel is notorious for ignoring the original style that numbers were entered with. So it is probably not necessary to add thousand separators there.

Re: From SAS to Excel - keeping number type while formatting

Multipla99 — Wed, 23 Nov 2022 09:04:41 GMT

It's the NLNUMw.d format under Swedish locale.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/nlsref/n15n1e10clx3qpn1vrnd0awyhd2s.htm

Re: From SAS to Excel - keeping number type while formatting

Multipla99 — Wed, 23 Nov 2022 12:54:10 GMT

Thank you, Cynthia! This really did the trick! I'm very excited!

Maybe you could also tell me what is the function of "_" in the "format:¥#,##0.00_" part?

Multipla99

Re: From SAS to Excel - keeping number type while formatting

Multipla99 — Wed, 23 Nov 2022 12:54:43 GMT

Thank you, Ksharp, for pointing me to this excellent blog post!

Multipla99

Re: From SAS to Excel - keeping number type while formatting

Multipla99 — Wed, 23 Nov 2022 12:53:29 GMT

Thank you very much, Tom!

Especially I appreciate you clarification that my national version of Excel will adapt this formatting to the national standard. Sometimes life is really easy!

Multipla99

Re: From SAS to Excel - keeping number type while formatting

Cynthia_sas — Thu, 24 Nov 2022 15:24:33 GMT

Hi:

That is a Microsoft rule about what characters can be used in a custom format. You can read about them here: https://www.thespreadsheetguru.com/blog/excel-custom-number-format-rules and of course, on the Microsoft site, which I couldn't find the link for. Here's a short list from the web page:

Hope this helps explain it. I rarely use the _ in my custom formats with TAGATTR, but that's just my preference.

Cynthia

Re: From SAS to Excel - keeping number type while formatting

Multipla99 — Thu, 24 Nov 2022 20:31:43 GMT

Thank you very much, Cynthia!

This link that you provided explains a lot. I'm also happy to hear that I'm not the only having difficulties to find this information on the Microsoft site.

All the best,
Multipla99