vertical total for each column using procedures

[BrahmanandaRao]

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;

[andreas_lds]

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.

[BrahmanandaRao]

[Kurt_Bremser]

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.

[BrahmanandaRao]

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;

[Kurt_Bremser]

So what is your question now?

[andreas_lds]

---

@BrahmanandaRao wrote:

---

Nice picture. And now please explain whether this is the expected output or, if not, what you expect to see.

[BrahmanandaRao]

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

[andreas_lds]

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.

[ballardw · Accepted solution]

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.