DATA Step, Macro, Functions and more

SAS 9.4, Excel and Conditional Formatting

Reply
Occasional Contributor
Posts: 7

SAS 9.4, Excel and Conditional Formatting

We have an issue with the SAS created Excel workbooks. If we select a row and apply "Conditional Formatting" the numbers in the row select shift to the left. When looking at the alignment format of one of the selected cells it says that it is right aligned. In addition to the shift of the data the excel alignment buttons are then non-functional. If we copy the workbook into a new workbook everything works fine. Does anyone know what is causing this?

Super User
Posts: 19,772

Re: SAS 9.4, Excel and Conditional Formatting

What exact version of Excel and SAS and how are you creating your Excel file?

Occasional Contributor
Posts: 7

Re: SAS 9.4, Excel and Conditional Formatting

Reeza,

Excel 2013 and SAS 9.4 M3

Occasional Contributor
Posts: 7

Re: SAS 9.4, Excel and Conditional Formatting

Using ODS to create the Excel file.

Super User
Posts: 19,772

Re: SAS 9.4, Excel and Conditional Formatting

What exact version of Excel and SAS and how are you creating your Excel file?


We need to be able to replicate your problem if we want to try and debug it. Ideally, you could provide code that references SASHELP table that demonstrates your issue.

Occasional Contributor
Posts: 7

Re: SAS 9.4, Excel and Conditional Formatting

Which SASHELP table would you like?

Occasional Contributor
Posts: 7

Re: SAS 9.4, Excel and Conditional Formatting

Here is the reference to the SASHELP tables in our log

 

SAS Log for Request 204204

MLOGIC(TITLESANDFOOTNOTES):  %IF condition %length(&suppressLogo) = 0 is TRUE
MPRINT(TITLESANDFOOTNOTES):   ods escapechar='^';
MPRINT(TITLESANDFOOTNOTES):   data _null_;
MPRINT(TITLESANDFOOTNOTES):   stop;
MPRINT(TITLESANDFOOTNOTES):   set sashelp.vdest;
MPRINT(TITLESANDFOOTNOTES):   where upcase(destination) not in ("HTML" "TAGSETS.EXCELXP" "LISTING");
MPRINT(TITLESANDFOOTNOTES):   call execute('ods ' || strip(destination) || ' text = "^S={preimage=""' ||

 

Super User
Posts: 19,772

Re: SAS 9.4, Excel and Conditional Formatting

I can replicate your issue in Office 2010 and SAS 9.4 M3

 

I don't see what's causing Excel to left align the cells, it seems to think it's text. You could trying using proc report and tagattr to force a numeric format on these columns in Excel and see if respects that. I can't see the difference between the copy and pasted version in Excel either in terms of format. 

 

Here's the code so someoene can replicate your issue:

 

ods excel file='C:\_localdata\temp\temp.xlsx';

proc print data=sashelp.cars;
run;

ods excel close;
Occasional Contributor
Posts: 7

Re: SAS 9.4, Excel and Conditional Formatting

Thank you.

Occasional Contributor
Posts: 7

Re: SAS 9.4, Excel and Conditional Formatting

We do force it using the tagattr.

Super User
Posts: 19,772

Re: SAS 9.4, Excel and Conditional Formatting

You haven't posted a code so I have no idea what you've done or tried. 

 

You can contact tech support and see if they're aware. I'm not sure this would be classified as a defect though, it does what's expected. 

Ask a Question
Discussion stats
  • 10 replies
  • 398 views
  • 0 likes
  • 2 in conversation