topic Re: vertical total for each column using procedures in SAS Programming

vertical total for each column using procedures

BrahmanandaRao — Wed, 02 Aug 2023 04:59:03 GMT

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;

Re: vertical total for each column using procedures

andreas_lds — Wed, 02 Aug 2023 05:35:57 GMT

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.

Re: vertical total for each column using procedures

BrahmanandaRao — Wed, 02 Aug 2023 06:07:09 GMT

Re: vertical total for each column using procedures

Kurt_Bremser — Wed, 02 Aug 2023 06:38:40 GMT

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.

Re: vertical total for each column using procedures

andreas_lds — Wed, 02 Aug 2023 06:39:11 GMT

@BrahmanandaRao wrote:

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

Re: vertical total for each column using procedures

BrahmanandaRao — Wed, 02 Aug 2023 06:51:03 GMT

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

Re: vertical total for each column using procedures

andreas_lds — Wed, 02 Aug 2023 06:56:38 GMT

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.

Re: vertical total for each column using procedures

BrahmanandaRao — Wed, 02 Aug 2023 09:10:17 GMT

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;

Re: vertical total for each column using procedures

Kurt_Bremser — Wed, 02 Aug 2023 10:06:53 GMT

So what is your question now?

Re: vertical total for each column using procedures

ballardw — Wed, 02 Aug 2023 15:11:10 GMT

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.