turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- proc report

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-30-2010 06:31 AM

Hi,

I have a dataset like

C0 C1 C2 C3

1 1 1 1

2 2 2 2

3 3 3 3

I want to create a report like

C0 C1 C2 C3 Total(rows)

1 1 1 1 3

2 2 2 2 6

3 3 3 3 9

Total(Cols) 5 5 5 15

Total(rows) is the sum of Col1,Col2,Col3 across rows.

Total(Cols) is the sum along column where Col0 not =1.

Plz help on implementing this.

I have a dataset like

C0 C1 C2 C3

1 1 1 1

2 2 2 2

3 3 3 3

I want to create a report like

C0 C1 C2 C3 Total(rows)

1 1 1 1 3

2 2 2 2 6

3 3 3 3 9

Total(Cols) 5 5 5 15

Total(rows) is the sum of Col1,Col2,Col3 across rows.

Total(Cols) is the sum along column where Col0 not =1.

Plz help on implementing this.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rose

12-30-2010 11:12 AM

Hi:

I'm confused about your data. What does C0 represent??? Are there any other identifying variables in your data, such as a name or category variable???

Also, I do not understand the logic of why total(cols), in the lower left-hand corner is 15. If you add 3 + 6 + 9, (the numbers in each row), you get 18, not 15. There is only 1 cell where C0 = 1. But, it also seems like you are adding the 1 for C1, C2 and C3 in the row total (of 3), but excluding them from the column total to get the 15??? That does not seem consistent with your statement that you want "Total(Cols) is the sum along column where Col0 not =1."

What other code have you tried?

cynthia

I'm confused about your data. What does C0 represent??? Are there any other identifying variables in your data, such as a name or category variable???

Also, I do not understand the logic of why total(cols), in the lower left-hand corner is 15. If you add 3 + 6 + 9, (the numbers in each row), you get 18, not 15. There is only 1 cell where C0 = 1. But, it also seems like you are adding the 1 for C1, C2 and C3 in the row total (of 3), but excluding them from the column total to get the 15??? That does not seem consistent with your statement that you want "Total(Cols) is the sum along column where Col0 not =1."

What other code have you tried?

cynthia

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rose

12-30-2010 11:47 PM

Hi,

I am not able to understand your requirements clearly.

Is 'c0' a lable?why Total(cols) is having value 5 instead of 6? dont you want to consider the first row while summing up?

Anyway try with the code given below.This might help you.

data temp;

input c0 c1 c2 c3;

datalines;

1 1 1 1

2 2 2 2

3 3 3 3

;

run;

proc sql;

create table work.temp1

as

select

temp.c0 as c0,

temp.c1 as c1,

temp.c2 as c2,

temp.c3 as c3,

sum(temp.c1,temp.c2,temp.c3) as totalrows

from temp;

quit;

proc report data=temp1;

define c0 /group;

define c1 /ANALYSIS SUM;

define c2 /ANALYSIS SUM;

define c3 /ANALYSIS SUM;

define totalrows /ANALYSIS SUM;

RBREAK AFTER /SUMMARIZE ;

run;

wish you a very happy new year!!

I am not able to understand your requirements clearly.

Is 'c0' a lable?why Total(cols) is having value 5 instead of 6? dont you want to consider the first row while summing up?

Anyway try with the code given below.This might help you.

data temp;

input c0 c1 c2 c3;

datalines;

1 1 1 1

2 2 2 2

3 3 3 3

;

run;

proc sql;

create table work.temp1

as

select

temp.c0 as c0,

temp.c1 as c1,

temp.c2 as c2,

temp.c3 as c3,

sum(temp.c1,temp.c2,temp.c3) as totalrows

from temp;

quit;

proc report data=temp1;

define c0 /group;

define c1 /ANALYSIS SUM;

define c2 /ANALYSIS SUM;

define c3 /ANALYSIS SUM;

define totalrows /ANALYSIS SUM;

RBREAK AFTER /SUMMARIZE ;

run;

wish you a very happy new year!!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

12-31-2010 12:33 PM

Hi:

There is actually no need for the PROC SQL step in your code. PROC REPORT, using a COMPUTE block, can calculate a value for a row total, as shown in the example below. The program creates 2 report items CALCTOT1 and CALCTOT2, based on the use of the SALES, INVENTORY and RETURNS variables from SASHELP.SHOES. I just made up some formulas, although a simple SUM function would work as well.

cynthia

[pre]

ods html file='c:\temp\calc_row_tot.html' style=sasweb;

proc report data=sashelp.shoes nowd;

title 'Method 1 - Create a computed column with a calculation';

where region in ('Asia', 'Canada', 'Pacific');

column region product sales inventory returns calctot1 calctot2;

define region / group;

define product / group;

define sales / sum 'Sales';

define inventory / sum 'Inventory';

define returns / sum 'Returns';

define calctot1 /computed 'Sales + Inventory/minus Returns' f=dollar12.;

define calctot2 / computed 'Some other formula';

compute calctot1;

calctot1 = sum(sales.sum, inventory.sum, (-1*returns.sum));

endcomp;

compute calctot2;

calctot2 = (sum(sales.sum, inventory.sum, returns.sum) / 100) * .05;

endcomp;

rbreak after / summarize;

break after region / summarize;

compute after region;

line ' ';

endcomp;

run;

ods html close;

[/pre]

There is actually no need for the PROC SQL step in your code. PROC REPORT, using a COMPUTE block, can calculate a value for a row total, as shown in the example below. The program creates 2 report items CALCTOT1 and CALCTOT2, based on the use of the SALES, INVENTORY and RETURNS variables from SASHELP.SHOES. I just made up some formulas, although a simple SUM function would work as well.

cynthia

[pre]

ods html file='c:\temp\calc_row_tot.html' style=sasweb;

proc report data=sashelp.shoes nowd;

title 'Method 1 - Create a computed column with a calculation';

where region in ('Asia', 'Canada', 'Pacific');

column region product sales inventory returns calctot1 calctot2;

define region / group;

define product / group;

define sales / sum 'Sales';

define inventory / sum 'Inventory';

define returns / sum 'Returns';

define calctot1 /computed 'Sales + Inventory/minus Returns' f=dollar12.;

define calctot2 / computed 'Some other formula';

compute calctot1;

calctot1 = sum(sales.sum, inventory.sum, (-1*returns.sum));

endcomp;

compute calctot2;

calctot2 = (sum(sales.sum, inventory.sum, returns.sum) / 100) * .05;

endcomp;

rbreak after / summarize;

break after region / summarize;

compute after region;

line ' ';

endcomp;

run;

ods html close;

[/pre]