BookmarkSubscribeRSS Feed
Malathi13
Obsidian | Level 7

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

2 REPLIES 2
PhilC
Rhodochrosite | Level 12

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.

atzamis
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 701 views
  • 0 likes
  • 3 in conversation