The SAS Output Delivery System and reporting techniques

SAS Add in for Microsoft Office Excel format displaying issue

Reply
Occasional Contributor
Posts: 15

SAS Add in for Microsoft Office Excel format displaying issue

Hi,

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

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!

SAS Super FREQ
Posts: 8,718

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

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
Valued Guide
Posts: 505

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

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.



Ask a Question
Discussion stats
  • 2 replies
  • 118 views
  • 0 likes
  • 3 in conversation