BookmarkSubscribeRSS Feed
rtjpl
Calcite | Level 5

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?

10 REPLIES 10
Reeza
Super User

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

rtjpl
Calcite | Level 5

Reeza,

Excel 2013 and SAS 9.4 M3

rtjpl
Calcite | Level 5

Using ODS to create the Excel file.

Reeza
Super User

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.

rtjpl
Calcite | Level 5

Which SASHELP table would you like?

rtjpl
Calcite | Level 5

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

 

Reeza
Super User

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;
rtjpl
Calcite | Level 5

Thank you.

rtjpl
Calcite | Level 5

We do force it using the tagattr.

Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1937 views
  • 0 likes
  • 2 in conversation