Proc Means and CSV file

[mnew]

Experts:
Another beginner question. Why would CSV output look differently from the listing and html outputs for Proc Means? I have a very simple dataset (one column for names, then 12 more columns for each month of 2011 to reflect monthly quantities). When I used proc means to get monthly totals, the listing and html outputs show the results vertically.
Such as
Month1 200
Month2 500
...
But I need the results in Excel so I added a CSV output destination (ODS CSVALL file=).
The CSV file listed results horizontally.
Month1 200 Month2 500 Month3 300 etc....

This horizontal layout is not as user friendly. Any advice?
Thank you!

[SPR]

Hello Mnew,

This is a solution:
[pre]
data i;
name="s"; month1=200; month2=400; output;
name="s"; month1=300; month2=500; output;
name="t"; month1=100; month2=300; output;
name="t"; month1=200; month2=400; output;
run;
proc means data=i noprint nway;
output out=s (drop=_:) mean=;
var month1 month2;
class name;
run;
proc transpose data=s out=t Prefix=Amt;
var name Month1 Month2;
run;
[/pre]
Sincerely,
SPR

[Cynthia_sas]

Hi:
Without seeing ALL of your code, and possibly LOG messages it is hard to comment. When I run this code, however, my CSV results are "vertical" and resemble the HTML and LISTING results. (Reusing an old MEANS example that showed how to use order=data with a character class variable build from a date.)

cynthia
[pre]
** make some data with a character variable for date;
data pricedata;
set sashelp.pricedata;
where year(date)=2002;
monthyr = put(date,mmddyy10.);
run;

proc sort data=pricedata;
by date;
run;

ods listing;
ods html file='c:\temp\someoutput_ht4.html' style=sasweb;
ods msoffice2k file='c:\temp\someoutput_mso.html' style=sasweb;
ods csvall file='c:\temp\someoutput_comma.csv';

proc means data=pricedata n sum mean;
title 'Proc Means Report on 2002 Months';
class monthyr/order=data;
var price;
run;

ods _all_ close;
[/pre]

[mnew]

Thanks SPR and Cynthia. I'll try out your suggestions.
Here is the code I used:
ods csvall file='C:\Documents and Settings\Mean.csv';
proc means data=work.test sum maxdec=0;
run;
ods csvall close;
The log message was normal looking.

NOTE: Writing HTML Body file: sashtml24.htm
NOTE: There were 55 observations read from the data set WORK.TEST.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.10 seconds
cpu time 0.07 seconds

[mnew]

Sorry, I should have posted a simple observation for the dataset too, in case my earlier description was not enough.

Name Month1 Month2 Month3 ... Month12
Amy 50 20 60 .... 100

[Cynthia_sas]

Hi:
What does WORK.TEST look like??? You are not using -any- CLASS or VAR statements. In this instance, PROC MEANS "stacks" the variables for LISTING, and HTML, but displays the results horizontally in CSV output.

You could fix this easily, and get the kind of CSV file you want, by switching to PROC TABULATE, as shown in the code below.

cynthia
[pre]
ods listing;
ods html file='c:\temp\nocontrol_ht4.html' style=sasweb;
ods msoffice2k file='c:\temp\nocontrol_mso.html' style=sasweb;
ods csvall file='c:\temp\nocontrol_comma.csv';

proc means data=sashelp.class sum maxdec=0;
title '1) Proc Means Report on All Numeric variables';
run;

proc tabulate data=sashelp.class f=comma6.0;
title '2) PROC TABULATE equivalent of MEANS output';
var _numeric_;
table _numeric_,
sum / box='Variable';
run;

ods _all_ close;

[/pre]

[mnew]

Thanks! I tried Proc Tabulate and it's a great idea!
The work.test dataset: only has one character variable field for Names. The rest 12 variables are numeric monthly quantities. I thought I didn't need to specify the variables as I just want the sum for each numeric variable. The dataset was originally in Excel and I could simply get everything there but I need to practice my beginner SAS skills :).

[Peter_C]

as you might guess, you're not the first to seek the original MEANS listing style output in a data set. Myra Olstik (and someone else who might be referred to as an "old stick") presented a paper on escaping from this ODS MEANS trap and the best version of the macro can be found on SAScommunity at
http://www.sascommunity.org/wiki/PROC_MEANS_-_Improve_on_the_default

[mnew]

Wow! I've registered for my first SAS Macro online training course. I'll have to study this magic macro solution again. Thanks!

[Cynthia_sas]

As I said, "stacked" values are supported for HTML and LISTING, but are not supported for CSV destinations. So ODS CSV/CSVALL "unstacks" the info (as a favor), since it can't do "stacking". And if you think about it, you probably wouldn't want all the values pumped into 1 Excel cell anyway.

PROC TABULATE gets around that limitation by generating a separate row for every one of your variables listed in the ROW dimension. The SUM statistic is in the COLUMN dimension of the table -- so for my example, there are 3 numeric variables in SASHELP.CLASS so I get 3 separate report rows from TABULATE.

On the other hand, instead of using CSV, you can "fool" the Windows registry into launching Excel for you by creating an ODS HTML or ODS MSOFFICE2K file and naming the file with a .XLS file extension. As you can see, in this case, Excel knows how to open and render the HTML file. (If you get a warning messages about the file extension not matching the file contents, just click "Yes" to open the file -- you will see this message if you are using Office 2010 and I think Office 2007.)

cynthia
[pre]
ods msoffice2k file='c:\temp\use_mso.xls' style=minimal;

proc means data=sashelp.class sum maxdec=0;
title '1) Proc Means Report on All Numeric variables';
run;

proc tabulate data=sashelp.class f=comma6.0;
title '2) PROC TABULATE equivalent of MEANS output';
var _numeric_;
table _numeric_,
sum / box='Variable';
run;

ods _all_ close;
[/pre]

[mnew]

Tried this. Great tip. (I also tried the Excel 2007 .xlsx just for fun, which did not work.) I will stay with this solution as I learn proc tabulate.
Thanks again.

[Cynthia_sas]

XLSX files are proprietary Office 2007 XML files. When you use ODS you have these choices:

1) use ODS CSV -- creates comma separated file that you can open with Excel 97 + higher
2) use ODS HTML -- creates HTML 4.0 files that you can open with Excel 97 + higher
3) use ODS MSOFFICE2K -- creates Microsoft 2000 "flavor" of HTML that you can open with Excel 2000 + higher
4) use ODS TAGSETS.EXCELXP -- creates Microsoft 2003 "flavor" of Spreadsheet XML that is different from the "new" Office 2007 "flavor" of XML.

Any of the files you create with #2, #3 or #4 can be named .XLS in order to "fool" the Windows registry into launching Excel when you double click on the file. However, .XLSX is a different type of XML and it is NOT created by any of the above methods. Excel really does care that this file extension is ONLY used for true Office 2007 XML "flavor" of files, named .XLSX.

As far as SAS in concerned the only way to create "true" .XLSX files is to use the LIBNAME Excel engine or PROC EXPORT with a SAS dataset using SAS 9.2 Phase 2 or higher. ODS cannot be used to create "true" .XLSX files. Excel, as you discovered, is very rigid in this regard -- for .XLSX files, the contents of the file MUST match the file extension. Excel is more flexible with HTML flavor of files, and Spreadsheet 2003 XML flavor of files. Excel has always been good about opening CSV files.

cynthia

[mnew]

I have to confess that I've been really fascinated by the use of Proc Tabulate in your earlier example. I read in SAS Help about the proc then tried to use it for one of my datasets.

proc tabulate data=plan.count_only out=work.T;
var Quantity_1-Quantity_12;
class program vehicle;
table program*vehicle,Quantity_1-Quantity_12;
run;

But in work.T the columns that have totals have longer names now ( such as Quantity_1_sum). My trouble is I have to divide these sums into weekly numbers. Now their names end with _sum, I can't really refer to them using a variable list for array processing for further calculations. I dug around Help hoping to either improve my Proc Tabulate or find a quick way to rename the variables together or maybe actually there is a way to refer to such a variable list...

What would you suggest? I don't mind just renaming them one by one... just thought there might be a shortcut to learn.
Thanks!

[Xiaoningdemao]

Hi Cynthia,

Your program:

proc tabulate data=sashelp.class f=comma6.0;
title '2) PROC TABULATE equivalent of MEANS output';
var _numeric_;
table _numeric_,
nmiss n / box='Variable';
run;

count non-missing values for numerical variable, I also want count for character variable, could help me modify this code to achive my goal?

Thanks a lot!

Best wishes,