Column Value Shows in Two Rows in Excel by Using ODS

deleted_user — Tue, 07 Oct 2008 09:42:06 GMT

I have this issue only with Enterprise Guild 4.

I used proc tabulate and created a table. One variable has two values: Non-Profitable Customers and Profitable Customer. When using ODS to export to the data to Excel, the value of "Non-Profitable Customers" will show as

row1 Non-Profit Profit Customer Total
row2 Customer

Does anyone know how to get Non-Profit Customers in one row (one cell)? Thanks!

Re: Column Value Shows in Two Rows in Excel by Using ODS

Cynthia_sas — Fri, 10 Oct 2008 02:39:51 GMT

Hi:
You didn't say which ODS destination you were using -- CSV, HTML or EXCELXP to get your procedure output into Excel. The fact is that ODS is not technically doing an "export" in the same way that PROC EXPORT does an "export".

ODS is creating an ASCII text file that Excel knows how to open and render -- it seems like a small point, but it often helps to look at the output file you're creating in Notepad to see what kind of markup tags are in the file before you open the file from ODS with Excel.

When I submit the program below, I do not observe the behavior that you describe when I open any of the 3 output files in Excel. I -do- have to drag the boundary for Column A to be bigger so that the full value of the NEWREG variable is visible.

If you don't get any ideas from the program below, then your best bet for help might be to contact Tech Support. They can look at your data and your program and help you come to some resolution.

cynthia
[pre]
options nodate nonumber center;

ods listing;

data shoes;
set sashelp.shoes;
if region in ('Asia', 'Pacific', 'Canada')
then newreg = 'Non Profitable Customers';
else newreg = 'Profitable Customers';
if product in ('Slipper', 'Boot');
run;

ods tagsets.excelxp file='SpreadsheetML.xls' style=sasweb;
ods msoffice2k file='MSO_HTML.xls' style=sasweb;
ods csvall file='CSV_file.csv';
proc tabulate data=shoes;
class newreg product;
var sales;
table newreg all,
product*sales*(n mean sum);
run;
ods _all_ close;
[/pre]

topic Re: Column Value Shows in Two Rows in Excel by Using ODS in ODS and Base Reporting

Column Value Shows in Two Rows in Excel by Using ODS

Re: Column Value Shows in Two Rows in Excel by Using ODS