BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yangtaotai
Calcite | Level 5

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



1 ACCEPTED SOLUTION
7 REPLIES 7
yangtaotai
Calcite | Level 5

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

ballardw
Super User

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.

yangtaotai
Calcite | Level 5

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

Reeza
Super User

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.

Cynthia_sas
SAS Super FREQ

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

yangtaotai
Calcite | Level 5

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

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
  • 7 replies
  • 2841 views
  • 6 likes
  • 4 in conversation