topic Re: Report Layout- Which Procedure to use in SAS Procedures

Report Layout- Which Procedure to use

tommy81 — Sun, 03 Oct 2010 15:19:37 GMT

Hi ,

I have the following data .
disp test test1
mm A1 123
mm B1 123
mm C1 123
mm D1 300
mm E1 0
mm F1 45
q1 A1 222
q1 B1 333
q1 C1 0
q1 D1 99
q1 E1 0
q1 F1 450
q2 A1 333
q2 B1 555
q2 C1 98
q2 D1 0
q2 E1 122
q2 F1 0
q3 A1 440
q3 B1 0
q3 C1 0
q3 D1 780
q3 E1 0
q3 F1 0
q4 A1 3
q4 B1 300
q4 C1 3
q4 D1 868
q4 E1 0
q4 F1 0

The report required is in the following format

Test mm q1 q2 q3 q4

A1 123 222 333 440 003
B1 123 333 555 000 300
C1 123 000 098 000 003

Total 369 555 986 440 306

D1 300 099 000 780 868

E1 000 000 122 000 000
F1 045 450 000 000 000

Total 045 450 122 000 000

The problem is the intermediate total to be displayed.
Here the 1st total is that of A1 B1 C1

and the last total is that of E1 F1

Looking at the Tabulate /Report i feel its impossible to have split totals
in such a format .

Please advice ,if its at all possible in SAS to have such layouts and how we could actually do it.

Re: Report Layout- Which Procedure to use

sbb — Sun, 03 Oct 2010 15:32:09 GMT

Investigate PROC REPORT (with the ACROSS option) and also possibly needing/wanting to use PROC TRANSPOSE on your input data to prepare it (converting vertical obs to horizontal).

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

proc report across site:sas.com

proc transpose site:sas.com

Re: Report Layout- Which Procedure to use

Peter_C — Sun, 03 Oct 2010 17:00:36 GMT

I notice you have no sub-total for D.
This is standard in proc print when a by-group has only one entry. For other procedures it would cause a bit more trouble.
I guess you want a proc print like
proc print label ;
by grouping ; * grouping abc together, D on its own and e,f together;
var test mm q1-q4 ;
run;
For this to work, you need
1 to create your your "grouping" variable for sorting the data
2 columns of mm and q1-q4
Can you do that in proc transpose or a data step?

Re: Report Layout- Which Procedure to use

tommy81 — Mon, 04 Oct 2010 08:36:01 GMT

Thankyou .
Infact I tried splitting the data in 3 and then combing .It worked as expected.

However I have format issue

Test mm q1 q2 q3 q4

A1 123 222 333 440 003
B1 123 333 555 000 300
C1 123 000 098 000 003

Total 369 555 986 440 306

D1 300 099 000 780 868

E1 $000 $000 $122 $000 $000
F1 $045 $450 $000 $000 $000

Total $045 $450 $122 $000 $000.

Would it be possible in SAS to get the above output with $ sign in the last 3 rows only ?

Re: Report Layout- Which Procedure to use

Peter_C — Mon, 04 Oct 2010 13:11:20 GMT

obviously in a data step you can control the formatting cel-by-cell
- if that is your need then you may have to go that way, along with ODS handling.

I see no excuse for such arbitrary formatting, so over to you

good luck
peterC

Re: Report Layout- Which Procedure to use

Cynthia_sas — Mon, 04 Oct 2010 15:34:29 GMT

Hi:
I agree with Peter -- I might understand treating the "Total" summary lines differently, but I don't understand how formatting the E1, F1 and only the last sub-total with $ adds anything to the report.

As Peter said, you could do this in a DATA step program. You can also use a CALL DEFINE statement in PROC REPORT to have a different format applied to a cell. But the down-side of the PROC REPORT approach is that you would also have a SUB-TOTAL for the D1 row. The program below uses a dataset called TESTDATA with a grouping variable called "GRPING" that is defined as a NOPRINT variable. Right now, the program puts $ on ALL the summary lines and then also puts the $ on the E1 and F1 rows. The logic will be a bit trickier if you want to ONLY put $ on the E1, F1 and last summary line. But if you're going to go down the DATA step road, you will have control over every cell you write.

cynthia

[pre]
data testdata;
length disp $2 test $8;
infile datalines;
input disp $ test $ test1;
if test in ('A1', 'B1', 'C1') then grping = 1;
else if test = 'D1' then grping = 2;
else if test in ('E1', 'F1') then grping = 3;
return;
datalines;
mm A1 123
mm B1 123
mm C1 123
mm D1 300
mm E1 0
mm F1 45
q1 A1 222
q1 B1 333
q1 C1 0
q1 D1 99
q1 E1 0
q1 F1 450
q2 A1 333
q2 B1 555
q2 C1 98
q2 D1 0
q2 E1 122
q2 F1 0
q3 A1 440
q3 B1 0
q3 C1 0
q3 D1 780
q3 E1 0
q3 F1 0
q4 A1 3
q4 B1 300
q4 C1 3
q4 D1 868
q4 E1 0
q4 F1 0
;
run;

ods listing close;
ods html file='c:\temp\calldef.html' style=sasweb;
proc report data=testdata nowd;
column grping test test1,disp;
title '1) With PROC REPORT -- no need to transpose, but you get a subtotal for D1';
define grping / group noprint;
define test / group;
define disp / across 'Disp As Across Var';
define test1 / sum f=comma14.0 ' ';
break after grping / summarize;
compute test1;
if (upcase(_break_)='GRPING') or
test in ('E1', 'F1') then do;
call define('_c3_','format','dollar14.0');
call define('_c4_','format','dollar14.0');
call define('_c5_','format','dollar14.0');
call define('_c6_','format','dollar14.0');
call define('_c7_','format','dollar14.0');
end;
endcomp;
compute after grping;
test = 'Total';
line ' ';
endcomp;
run;
ods _all_ close;
[/pre]

Re: Report Layout- Which Procedure to use

tommy81 — Tue, 05 Oct 2010 17:36:56 GMT

Hi Cinthia,

Thanku somuch,but in my report i do not need the 2nd Total field as it would make it look meaningless.

I was actually looking if it was possible in a single procedure.In the end I had to use a combination