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!
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.
options nodate nonumber center;
if region in ('Asia', 'Pacific', 'Canada')
then newreg = 'Non Profitable Customers';
else newreg = 'Profitable Customers';
if product in ('Slipper', 'Boot');
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;
table newreg all,
product*sales*(n mean sum);
ods _all_ close;