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
- /
- Report Layout- Which Procedure to use

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

10-03-2010 11:19 AM

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.

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.

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

Posted in reply to tommy81

10-03-2010 11:32 AM

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

Scott Barry

SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

proc report across site:sas.com

proc transpose site:sas.com

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

Posted in reply to tommy81

10-03-2010 01:00 PM

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?

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?

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

Posted in reply to Peter_C

10-04-2010 04:36 AM

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 ?

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 ?

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

Posted in reply to tommy81

10-04-2010 09:11 AM

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

- 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

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

Posted in reply to tommy81

10-04-2010 11:34 AM

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]

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]

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

Posted in reply to Cynthia_sas

10-05-2010 01:36 PM

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

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