SAS Add in for Microsoft Office Excel format displaying issue

[AaroninMN]

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!

[Cynthia_sas]

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

[rogerjdeangelis]

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.