topic Re: Proc Report Column in SAS Procedures

Proc Report Column

Malathi13 — Mon, 30 Jan 2017 17:19:32 GMT

Hi,

I'm trying to generate a sales report for each quarter (2015Q1, 2015Q2, 2015Q3 and 2015Q4) with Net sales for each product. I have a varaible called Prod_cat (product category) and prod_grp (product group). I want to nest Prod_cat and prod_grp so that for each Prod_cat I will have the groups( for ex: Prod_cat have RX and under this category I have Derm, Vision and Cancer). I'm trying to use proc report and generate Columns such as Prod_Cat, Quarter (Across) and Netsales (on the right side of the Column and at the bottom for each quarter. The table should look like this:

Prod_Cat	2015Q1	2015Q2	2015Q3	2015Q4	NetSales
RX
Derm	100,000	105,000	200,000	325,000	730,000
Vision	120,000	102,256	250,200	155,200	627,656
Ears	123,000	205,200	260,100	183,245	771,545
Netsales	343,000	412,456	710,300	663,445	2,129,201

Can someone help me in getting this kind of a report using proc report. I'm just confused with the columns statement so that I get the Prod_cat and under that I get the Prod_grp and Netsales at the bottom and on the side.

Thank you

Malathi

Re: Proc Report Column

PhilC — Wed, 08 Feb 2017 19:54:47 GMT

After a week , I hope I'm not too late to help. What does your datasets look like? Are you open to using proc tabulate; this is my go-to-procedure for a sumarization/pivot-table like this.

Re: Proc Report Column

atzamis — Thu, 09 Mar 2017 21:56:38 GMT

Hi,

although unsure about your data set's structure :

data have;
format prod_grp $9.;
input dt date9. Prod_Cat $ prod_grp $ NetSales;
datalines;
31mar2015 RX Derm 100000
30JUN2015 RX Derm 105000
30SEP2015 RX Derm 200000
31DEC2015 RX Derm 325000
31mar2015 RX Vision 120000
30JUN2015 RX Vision 102256
30SEP2015 RX Vision 250200
31DEC2015 RX Vision 155200
31mar2015 RX Ears 123000
30JUN2015 RX Ears 205200
30SEP2015 RX Ears 260100
31DEC2015 RX Ears 183245
;
RUN;

PROC REPORT DATA=HAVE NOWD MISSING;
COLUMNS ('Prod_Cat' Prod_Cat) ('prod_grp' prod_grp) (DT, NETSALES) ('Net Sales' NETSALES=NETSALESF);
DEFINE Prod_Cat / GROUP noprint '' ;
DEFINE prod_grp / GROUP '' f=$9.;
DEFINE DT / ACROSS F=YYQ. '';
DEFINE NETSALES/ ANALYSIS SUM F=COMMA12. '';
DEFINE NETSALESF/ ANALYSIS SUM F=COMMA12. '';
compute before Prod_Cat /style={just=l};
line Prod_Cat $5. ;
endcomp;
rbreak after / summarize ;
compute after ;
prod_grp ='Net Sales';
endcomp;

RUN;

prod_grp 2015Q1 2015Q2 2015Q3 2015Q4 Net Sales

RX
Derm	100,000	105,000	200,000	325,000	730,000
Ears	123,000	205,200	260,100	183,245	771,545
Vision	120,000	102,256	250,200	155,200	627,656
Net Sales	343,000	412,456	710,300	663,445	2,129,201