Help using Base SAS procedures

Proc Report Column

Reply
Contributor
Posts: 46

Proc Report Column

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_Cat2015Q12015Q22015Q32015Q4NetSales
RX     
    Derm100,000105,000200,000325,000730,000
   Vision120,000102,256250,200155,200627,656
   Ears123,000205,200260,100183,245771,545
Netsales343,000412,456710,300663,4452,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

Regular Contributor
Posts: 156

Re: Proc Report Column

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.

Occasional Contributor
Posts: 10

Re: Proc Report Column

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
Derm100,000105,000200,000325,000730,000
Ears123,000205,200260,100183,245771,545
Vision120,000102,256250,200155,200627,656
Net Sales343,000412,456710,300663,4452,129,201
Ask a Question
Discussion stats
  • 2 replies
  • 152 views
  • 0 likes
  • 3 in conversation