- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Guys ,
I want to find totals for each column wise using procedures can we get solutions using proc means ,proc summary ,proc tabulate proc report
here i am trying proc report but i did not get
proc report data=sales;
column sales_id year2010 year2013 ;
define sales_id/display;
define year2010/analysis sum ;
define year2013/analysis sum ;
compute after;
sales_id='Total';
endcomp;
rbreak after/summarize;
run;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Making some serious assumptions about the content of your data:
Means and Summary use the same syntax for what you want. I use Summary from habit to create output data sets.
Proc summary data=sales; class sales_id; var year2010 year2013; output out=summary (drop=_freq_) sum=; run;
There will be a variable _type_ that shows the combinations of the CLASS variables. With one class variable it will have values of 0 and 1. 0 will be the overall data set total. The Sales_id value will also be missing.
Without explicit data I won't attempt to go through how to get the total row at the bottom but Proc Sort/Print and a format to display text might work.
Proc tabulate data=sales; class sales_id; var year2010 year2013; table sales_id All='Total', (Year2010 Year2013)* sum ; run;
All is used to get statistics for "all values" of a class variable when placed correctly. The ='text' allows you to provide a label for the All row. You could use All='All Sales ID' . You can use =' ' (blank text) to suppress text appearing as well. Sum=' ' would suppress the column heading Sum for example.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Because you don't show data and the result, you have to explain in more detail what you expect and what you got.
Maybe the problem can be solved by using "group" instead of "display" for sales_id.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Looks like sales_id is numeric, so you cannot store a character value like "Total" in it.
Make it character, or define a computed column of type character.
Hint: identifiers should always be stored as character, even if they contain only digits.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Kurt,
Thank your for your solution change datatype numeric to character i understood we can achieved below task only proc report rather than other procedures namely proc means proc tabulate proc summary
data sales;
input sales_id $ year2010 year2013;
datalines;
1 566 666
2 866 489
3 677 1473
4 99 547
5 666 234
6 147 55
7 447 87
;
run;
proc report data=sales;
column sales_id year2010 year2013 ;
define sales_id/display;
define year2010/analysis sum ;
define year2013/analysis sum ;
compute after;
sales_id='Total';
endcomp;
rbreak after/summarize;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@BrahmanandaRao wrote:
Nice picture. And now please explain whether this is the expected output or, if not, what you expect to see.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Expected output
ales_id | year2010 | year2013 |
---|---|---|
1 | 566 | 666 |
2 | 866 | 489 |
3 | 677 | 1473 |
4 | 99 | 547 |
5 | 666 | 234 |
6 | 147 | 55 |
7 | 447 | 87 |
TOTAL | 3468 | 3551 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So @Kurt_Bremser already explained the problem and what to do: you have to convert the variable into alphanumeric, this has to happen in a step before proc report.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Making some serious assumptions about the content of your data:
Means and Summary use the same syntax for what you want. I use Summary from habit to create output data sets.
Proc summary data=sales; class sales_id; var year2010 year2013; output out=summary (drop=_freq_) sum=; run;
There will be a variable _type_ that shows the combinations of the CLASS variables. With one class variable it will have values of 0 and 1. 0 will be the overall data set total. The Sales_id value will also be missing.
Without explicit data I won't attempt to go through how to get the total row at the bottom but Proc Sort/Print and a format to display text might work.
Proc tabulate data=sales; class sales_id; var year2010 year2013; table sales_id All='Total', (Year2010 Year2013)* sum ; run;
All is used to get statistics for "all values" of a class variable when placed correctly. The ='text' allows you to provide a label for the All row. You could use All='All Sales ID' . You can use =' ' (blank text) to suppress text appearing as well. Sum=' ' would suppress the column heading Sum for example.