BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Anandkvn
Lapis Lazuli | Level 10

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;
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

9 REPLIES 9
andreas_lds
Jade | Level 19

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.

Kurt_Bremser
Super User

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.

Anandkvn
Lapis Lazuli | Level 10

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;

 

andreas_lds
Jade | Level 19

@Anandkvn wrote:

Anandkvn_0-1690956405755.png

 


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

Anandkvn
Lapis Lazuli | Level 10

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
Jade | Level 19

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
Super User

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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