Need help in proc report

Aman4SAS — Wed, 11 Dec 2013 18:07:23 GMT

data grocery;

input Sector $ Manager $ Department $ Sales @@;

datalines;

se 1 np1 50 se 1 p1 100 se 1 np2 120 se 1 p2 80

se 2 np1 40 se 2 p1 300 se 2 np2 220 se 2 p2 70

nw 3 np1 60 nw 3 p1 600 nw 3 np2 420 nw 3 p2 30

nw 4 np1 45 nw 4 p1 250 nw 4 np2 230 nw 4 p2 73

nw 9 np1 45 nw 9 p1 205 nw 9 np2 420 nw 9 p2 76

sw 5 np1 53 sw 5 p1 130 sw 5 np2 120 sw 5 p2 50

sw 6 np1 40 sw 6 p1 350 sw 6 np2 225 sw 6 p2 80

ne 7 np1 90 ne 7 p1 190 ne 7 np2 420 ne 7 p2 86

ne 8 np1 200 ne 8 p1 300 ne 8 np2 420 ne 8 p2 125

;

proc format library=proclib;

value $sctrfmt 'se' = 'Southeast'

'ne' = 'Northeast'

'nw' = 'Northwest'

'sw' = 'Southwest';

value $mgrfmt '1' = 'Smith' '2' = 'Jones'

'3' = 'Reveiz' '4' = 'Brown'

'5' = 'Taylor' '6' = 'Adams'

'7' = 'Alomar' '8' = 'Andrews'

'9' = 'Pelfrey';

value $deptfmt 'np1' = 'Paper'

'np2' = 'Canned'

'p1' = 'Meat/Dairy'

'p2' = 'Produce';

run;

options nodate pageno=1 linesize=64 pagesize=60

fmtsearch=(proclib);

proc report data=grocery nowd headline headskip;

column sector manager sales;

define sector / group

format=$sctrfmt.

'Sector';

define manager / group

format=$mgrfmt.

'Manager';

define sales / analysis sum

format=comma10.2

'Sales';

break after sector / ol

summarize

suppress

skip;

compute after;

line 'Combined sales for the northern sectors were '

sales.sum dollar9.2 '.';

endcomp;

compute sales;

if _break_ ne ' ' then

call define(_col_,"format","dollar11.2");

endcomp;

where sector contains 'n';

title 'Sales Figures for Northern Sectors';

run;

Output

Sales Figures for Northern Sectors

Sector Manager Sales

-----------------------------------------------

Northeast Alomar 786.00

Andrews 1,045.00

----------

$1,831.00

Northwest Brown 598.00

Pelfrey 746.00

Reveiz 1,110.00

----------

$2,454.00

Combined sales for the northern sectors were $4,285.00.

My desired output is....

Sales Figures for Northern Sectors

Manager Sales

--------------------------------

Alomar $786.00

Andrews $1,045.00

Northeast $1,831.00

Brown $598.00

Pelfrey $746.00

Reveiz $1,110.00

Northwest $2,454.00

- Total $4,285.00

can i have output like this,

Please guide me .. thanks...........

Re: Need help in proc report

Cynthia_sas — Wed, 11 Dec 2013 18:29:12 GMT

Hi,

You seem fairly comfortable with COMPUTE blocks. So you would put the NOPRINT option on the DEFINE statement SECTOR. Then take SUPPRESS option off your BREAK statement for SECTOR.

Next, you would need to have a compute block for MANAGER, something like this (not on a system where I have SAS right now):

compute after manager;

manager = sector;

endcomp;

I think you will need to make the Manager variable have a LENGTH statement in the DATA step code, thoough, or I belive that Northeast and Northwest might not fit. To get the Grand Total, you will need an RBBREAK AFTER / SUMMARIZE statement added and then add

Manager='Total' to your compute blck for compute after.

I have an example that uses SASHELP.SHOES, but I'm not on the computer with the example right now.. However, those ideas should get you pointed in the right direction.

cynthia

topic Re: Need help in proc report in SAS Procedures

Need help in proc report

Re: Need help in proc report