The SAS Output Delivery System and reporting techniques

Sub-totals in Excel

Reply
Contributor
Posts: 33

Sub-totals in Excel

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
SAS Super FREQ
Posts: 8,745

Re: Sub-totals in Excel

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]
Contributor
Posts: 33

Re: Sub-totals in Excel

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Sub-totals in Excel

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.
Contributor
Posts: 33

Re: Sub-totals in Excel

Thank you Scott
Lee
Ask a Question
Discussion stats
  • 4 replies
  • 173 views
  • 0 likes
  • 3 in conversation