BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
1 REPLY 1
Cynthia_sas
SAS Super FREQ
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]

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
  • 1 reply
  • 571 views
  • 0 likes
  • 2 in conversation