topic Sub-totals in Excel in ODS and Base Reporting

Sub-totals in Excel

LeeJenson — Wed, 03 Feb 2010 21:32:08 GMT

I would like to create sub-totals in excel from my data set. In my original dataset my data is like this where col3 is a sum of col2 by col1

col1 col2 col3
AAA 1 4
AAA 1 4
AAA 2 4
BBB 1 5
BBB 3 5
BBB 1 5

I would like this data to appear in my excel spreadsheet like this

AAA 1
AAA 1
AAA 2
--
4

BBB 1
BBB 3
BBB 1
--
5

..but I am not sure if I need to do something with source dataset first or
in excel after it has been imported?

Thanks
Lee Message was edited by: Lee2010

Re: Sub-totals in Excel

Cynthia_sas — Wed, 03 Feb 2010 21:54:50 GMT

Hi:
It sounds like you want a report in Excel. Have you tried PROC PRINT with the SUM statement???

Do you know which technique you're going to use to get your output into Excel??? You basically have 3 choices using ODS:
1) creating a comma-separated file using ODS CSV or ODS CSVALL
2) creating an HTML file using ODS HTML, ODS HTML3 or ODS MSOFFICE2K
3) creating an XML file that follows the Microsoft Spreadsheet Markup Language XML specification for how to describe a workbook and worksheets using ODS TAGSETS.EXCELXP

Some examples of using each of these 3 techniques is shown in an answer to your previous posting.

As to what procedure you use, I suggest you try a PROC PRINT something like this (code not tested because there's not enough information about your destination to generate working ODS code).

cynthia
[pre]

ods file=''
style=sasweb;

proc print data=lib.yourdata;
var col1 col2;
sum col2;
run;

ods close;
[/pre]

Re: Sub-totals in Excel

LeeJenson — Thu, 04 Feb 2010 09:40:10 GMT

Hello Cynthia,

Thanks for your reply. The only method I am using to export my dataset to Excel is a PROC EXPORT. I think this is where I am going wrong as I am not using any ODS type commands.

Thanks
Lee

Re: Sub-totals in Excel

sbb — Thu, 04 Feb 2010 10:04:19 GMT

Consider the opportunity to have a separate XLS document containing only a pivot-table report (your summary information), and the XLS documents opens and imports an external CSV file. You can then keep your SAS-exported data separate from the Excel document that does the summarization and report generation, if that is your reference. For your SAS execution, you would want to explore using PROC EXPORT directly to a CSV-format (or XLS directly).

Scott Barry
SBBWorks, Inc.

Re: Sub-totals in Excel

LeeJenson — Thu, 04 Feb 2010 17:21:38 GMT

Thank you Scott
Lee