topic Re: proc report in SAS Procedures

proc report

Rose — Thu, 30 Dec 2010 11:31:38 GMT

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.

Re: proc report

Cynthia_sas — Thu, 30 Dec 2010 16:12:02 GMT

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

Re: proc report

deleted_user — Fri, 31 Dec 2010 04:47:25 GMT

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!!

Re: proc report

Cynthia_sas — Fri, 31 Dec 2010 17:33:26 GMT

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]