topic SAS Add in for Microsoft Office Excel format displaying issue in ODS and Base Reporting

SAS Add in for Microsoft Office Excel format displaying issue

AaroninMN — Fri, 31 Mar 2017 15:51:27 GMT

Hi,

I'm failry new to SAS Add in for Microsoft Excel so please be gentle. 🙂

Background info Using Excel 2010 and running SAS Enterprise Guide 7.1 version 7.13 HF3

Each week I update an Excel report that contains month-to-date data calculated as a percentage. My SAS data set that I am retrieving the information from is formatted as PercentN10.1. When I select the refresh button for SAS in Excel, the columns in the excel file will often display with hundreths visisble instead of only tenths.

Is there a setting I can change so I do not have to adjust formats each week after refreshing?

Ex.

before 3.2% after 3.20% ( I have to adjust visible decimals after refreshing)

Thanks in advance!

Re: SAS Add in for Microsoft Office Excel format displaying issue

Cynthia_sas — Fri, 31 Mar 2017 17:20:33 GMT

Hi:
If you are pulling the data directly into Excel from SAS, then you may have to manually supply the format.

If you are using ODS, such as in a stored process, to do your report, then you may be able to control your percent. Unfortunately, Excel does not always respect the SAS formats.
cynthia

Re: SAS Add in for Microsoft Office Excel format displaying issue

rogerjdeangelis — Sun, 02 Apr 2017 18:25:59 GMT

There are several ways to update an excel sheet in place and maintain existing formatting. 'proc import/export',  ODS excel and xmltagsets are not able to update an existing sheet keeping the formatting, SAS Libname, Python, R and Peal can.

I don't use MS office add ons and I am not sure if 'proc import/export' can update in place or drop a sheet and repopulate.

I have quite a few posts on updating excel in place.