topic Re: SAS 9.4, Excel and Conditional Formatting in SAS Programming

SAS 9.4, Excel and Conditional Formatting

rtjpl — Fri, 06 Jan 2017 16:28:18 GMT

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?

Re: SAS 9.4, Excel and Conditional Formatting

Reeza — Fri, 06 Jan 2017 16:32:25 GMT

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

Re: SAS 9.4, Excel and Conditional Formatting

rtjpl — Fri, 06 Jan 2017 16:45:18 GMT

Reeza,

Excel 2013 and SAS 9.4 M3

Re: SAS 9.4, Excel and Conditional Formatting

Reeza — Fri, 06 Jan 2017 16:49:57 GMT

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.

Re: SAS 9.4, Excel and Conditional Formatting

rtjpl — Fri, 06 Jan 2017 16:50:22 GMT

Using ODS to create the Excel file.

Re: SAS 9.4, Excel and Conditional Formatting

rtjpl — Fri, 06 Jan 2017 18:14:31 GMT

Which SASHELP table would you like?

Re: SAS 9.4, Excel and Conditional Formatting

rtjpl — Fri, 06 Jan 2017 18:39:25 GMT

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=""' ||

Re: SAS 9.4, Excel and Conditional Formatting

Reeza — Fri, 06 Jan 2017 19:04:57 GMT

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;

Re: SAS 9.4, Excel and Conditional Formatting

rtjpl — Fri, 06 Jan 2017 19:19:38 GMT

Thank you.

Re: SAS 9.4, Excel and Conditional Formatting

rtjpl — Fri, 06 Jan 2017 20:12:33 GMT

We do force it using the tagattr.

Re: SAS 9.4, Excel and Conditional Formatting

Reeza — Fri, 06 Jan 2017 20:45:10 GMT

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.