Proc print output to Excel with ODS gives errors and adds blanc columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Proc print output to Excel with ODS gives errors and adds blanc columns

Hi all,

I'm building a report with output to Excel using ODS and proc print.

If I have 26 columns or more than 26 columns for output to excel, the workbook gives errors on opening, saying there is unreadable content.

Upon opening the workbook I see that the column that should have been in column Z gets moved to column AZ and everything after that gets removed.

I have created a simple testprogram to demonstrate this:

data INPUT;

input COL1 $1. COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11 COL12 COL13 COL14 COL15 COL16 COL17 COL18 COL19 COL20 COL21 COL22 COL23 COL24 COL25 COL26 COL27 COL28 COL29 COL30 ;

datalines;

A 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

B 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

C 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

D 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

E 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

F 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

G 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

;

run;

/*============*/

ods excel file="C:\temp\Testexcel.xlsx"

options(

sheet_interval="none"

sheet_name="Test"

);

PROC print DATA=WORK.INPUT;

VAR COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11 COL12 COL13 COL14 COL15 COL16 COL17 COL18 COL19 COL20 COL21 COL22 COL23 COL24 COL25 COL26 COL27 COL28 COL29 COL30;

RUN;

ods excel close;

Can anyone help me fix this ?

Thanks,


Accepted Solutions
Solution
‎03-03-2015 09:54 AM
Super Contributor
Posts: 394

Re: Proc print output to Excel with ODS gives errors and adds blanc columns

I see the behavior you described when using the destination for Excel at SAS 9.4M1. This bug is fixed in 9.4M2. The destination for Excel is "pre-production" in both of these releases, so bugs are expected. The destination for Excel will be production in 9.4M3.

The solution is to upgrade to 9.4M2 or switch to the ExcelXP tagset as Steelers_In_DC suggests.

View solution in original post


All Replies
Valued Guide
Posts: 856

Re: Proc print output to Excel with ODS gives errors and adds blanc columns

I don't recall ever having that many columns but try this example and see if it helps.  I am unable to test it myself right now:

ods _all_ close;

ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml'

style=printer;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME' AUTOFILTER = 'ALL');

proc print data=FILE noobs;

var _all_;run;quit;

ods tagsets.ExcelXP close;

Solution
‎03-03-2015 09:54 AM
Super Contributor
Posts: 394

Re: Proc print output to Excel with ODS gives errors and adds blanc columns

I see the behavior you described when using the destination for Excel at SAS 9.4M1. This bug is fixed in 9.4M2. The destination for Excel is "pre-production" in both of these releases, so bugs are expected. The destination for Excel will be production in 9.4M3.

The solution is to upgrade to 9.4M2 or switch to the ExcelXP tagset as Steelers_In_DC suggests.

Occasional Contributor
Posts: 13

Re: Proc print output to Excel with ODS gives errors and adds blanc columns

Hi Tim, Steelers,

Thank you for your answers.

We have upgraded to M2 and indeed the issue is solved now.

Regular Learner
Posts: 1

Re: Proc print output to Excel with ODS gives errors and adds blanc columns

Hi Tim,

 

I'm having the same issue but can't upgrade because of our local corporate policy and can't use ExcelXP since I need the graphics embedded in the file.  Is there any way to identify what data is causing the problem?

 

Thanks, 

Alex

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 595 views
  • 3 likes
  • 4 in conversation