The SAS Output Delivery System and reporting techniques

How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

Hi Everyone,

As title says, I want to export some data to excel as .xlsx file. At very beginning, what I dd for a small data set (less than 20 columns) like something below:

ods excel file="XXXXXXX.xlsx"  options(sheet_name="test1");

proc report data=work.updated_weely_summary nowd;

columns col1 col2 col3 ..........................col 20;

define col1/display style={font_size=5 color=cyan};


run;

ods excel close;

The result looks great and it is what I wanted to see.

However, when I started to do the same thing to a data table with 26 or more than 26 columns, there problem happened

  1. "unreadable content" when open the excel file;
  2. when you accept the error and click yes and open the excel file, the 26th column which should be under Z column in excel is always be allocated to AX column;


Originally, I thought the problems above are caused by my code, maybe columns names, or format/informat, but when I tested different tables with more than 26 cols, the output excel all have the same issue, no matter you choose .xlsx or .xls.  Then I did some research online and found out that the ods excel actually create a xml file which can be read by excel;  some old posts related with .xlsx output are here:    and the a hot fix is here: 47105 - "Unreadable content" errors occur when you export files using the XLSX driver


However, what i am trying to do is to output a .xlsx and also define the style in the excel file ( I always get unreadable content error and wrong location for column number of 25) and seems like ods excel with Proc report is my best chance.

Just want to know if there is a way I can do this???????????

I am using EG 6.1 now and excel 2010;

Thank you,

Tao




Accepted Solutions
Solution
‎03-19-2015 01:47 PM
Super User
Posts: 17,818

Re: How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?


All Replies
Solution
‎03-19-2015 01:47 PM
Super User
Posts: 17,818

Re: How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

Occasional Contributor
Posts: 19

Re: How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

HI Reeza,

Thank you for your help and I have read the Chris's blog before and since the article was posted on Aug 2014, so I thought it would be more stable now.  I guess I am too optimistic about the ods excel thing Smiley Sad

Tao

Super User
Posts: 10,497

Re: How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

I believe ODS EXCEL is still sort of experimental.

You could use ODS tagsets.excelxp which yes does create xml. But it has been around longer and is a bit more stable.

With this tagset you also have an option on your cells of tag_attr in the style overrides that lets you use Excel formatting.

Occasional Contributor
Posts: 19

Re: How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

Hi Ballardw,

Thank you for your reply. I tried some simple examples by using Ods tagsets.excelxp but not sue if I can do something like  Compute in proc report with Tagsets.excelxp.

proc report data=xxx;

column ......;

define col1_Flag/noprint;

compute col1_Flag;

if col1_Flag>0 then do;

call define ('_c21_', 'style','style={background=green}');

end;

else if col1_Flag<=0 then do;

call define ('_c21_', 'style','style={background=red}');

end;

endcomp;

end;

Tao

Super User
Posts: 17,818

Re: How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

Tagsets.ExcelXP can do traffic highlighting. See Vince Del Gobbo's paper if you need specific references. There's also a VB script that will convert the XML file to a native XLSX file on the SAS support page.

SAS Super FREQ
Posts: 8,743

Re: How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

Hi:

  What you want to do in your COMPUTE block does look do-able. However, in this case, the order that the variables are listed in your COLUMN statement in PROC REPORT will determine whether what you want to do is possible. It has nothing to do with TAGSETS.EXCELXP. For example given this COLUMN statement:

column name age height weight;

  A COMPUTE block on NAME could NOT change the style of AGE, HEIGHT or WEIGHT. This is because PROC REPORT builds each report row by working from left to right based on the COLUMN statement. So, at the point in time when NAME is being written to the report row, AGE, HEIGHT and WEIGHT have not yet been written to the report row. But given the same COLUMN statement, a COMPUTE block on WEIGHT (the last variable in the COLUMN list) could test for the value of NAME and could also change the style of any variable that appeared before it on the COLUMN statement. So in the COMPUTE block for weight, you could alter the style of NAME, AGE, HEIGHT or WEIGHT. This is fundamental PROC REPORT behavior -- for any destination -- nothing to do with TAGSETS.EXCELXP or HTML or PDF or RTF.

cynthia

Occasional Contributor
Posts: 19

Re: How use SAS ODS excel to export data with style (traffic light etc. for cells in excel) as xlsx file?

Cynthia,

Thank you for your advice, Cynthia! Yes, I have done some Proc report before by creating pdf, html output and they all work fine with styles and compute block. I will try this with TagSets.Excelxp and see how it  goes.

Tao

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1167 views
  • 6 likes
  • 4 in conversation