BookmarkSubscribeRSS Feed
LeeJenson
Calcite | Level 5
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
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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]
LeeJenson
Calcite | Level 5
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
LeeJenson
Calcite | Level 5
Thank you Scott
Lee

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
  • 4 replies
  • 974 views
  • 0 likes
  • 3 in conversation