Desktop productivity for business analysts and programmers

Fitting Proc Means to one excel worksheet

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 129
Accepted Solution

Fitting Proc Means to one excel worksheet

Hi All,

 

I have a proc means that creates multiple tables. I use ODS Excel to export it to excel.

When it exports it creates a new tab for each table and I would like all the tables to appear on one worksheet.

 

Or ultimately If I can actually have all the data appear in one table.

 

 

Proc means data=work.acw_abs_baseline median Mean q1 q3 stddev;
By Cluster Queue_cd;
VAR AWT;
Format AWT Comma9.0;
Run;

Current ouput

 

Cluster=ABS Queue Code=ABSa
     
The MEANS Procedure
     
Analysis Variable : AWT
MedianMeanLower
Quartile
Upper
Quartile
Std Dev
107.3750000138.586248058.7692308174.2857143142.0509725

 

 

Like the Output to look like this?  

       
Analysis Variable : AWT
Cluster CodeMedianMeanLower
Quartile
Upper
Quartile
Std Dev
ABS ABSa107.3750000138.586248058.7692308174.2857143142.0509725
ABS ABSb67.0000000121.475424617.6666667143.0000000275.3427533
DSCDSCa251.0000000327.7871022134.0000000421.5000000329.8855516
DSCDSCb226.3333333288.4081027114.0000000387.0000000278.9890103

 

Cheers

 

Dean

 

 

 


Accepted Solutions
Solution
‎09-13-2017 10:30 PM
Super User
Posts: 24,010

Re: Fitting Proc Means to one excel worksheet

1. Use ODS EXCEL with the SHEET_INTERVAL='none' option to have all the results on one table.

2. To get a 'simplified' table, run PROC MEANS using the STACKODS option - capture the table and then use PROC PRINT to export the results to your excel file using ODS EXCEL. 

You can either do it before the ODS EXCEL to avoid duplicate output or use ods exclude all to suppress the results.

 

See an example of the STACKODS options here:

http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#p17h6q7ygvkl1sn13qzf9...

View solution in original post


All Replies
Solution
‎09-13-2017 10:30 PM
Super User
Posts: 24,010

Re: Fitting Proc Means to one excel worksheet

1. Use ODS EXCEL with the SHEET_INTERVAL='none' option to have all the results on one table.

2. To get a 'simplified' table, run PROC MEANS using the STACKODS option - capture the table and then use PROC PRINT to export the results to your excel file using ODS EXCEL. 

You can either do it before the ODS EXCEL to avoid duplicate output or use ods exclude all to suppress the results.

 

See an example of the STACKODS options here:

http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#p17h6q7ygvkl1sn13qzf9...

PROC Star
Posts: 1,334

Re: Fitting Proc Means to one excel worksheet

Another option: Use the CLASS statement in PROC MEANS...

 

proc means data=sashelp.class median Mean q1 q3 stddev nway;
class name sex;
var height;
run;
Frequent Contributor
Posts: 129

Re: Fitting Proc Means to one excel worksheet

Thanks Tom for your help again.

 

Cheers

 

Dean

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 1065 views
  • 2 likes
  • 3 in conversation