BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ak2
Calcite | Level 5 ak2
Calcite | Level 5

Hi! Can anyone help me with how to add percentage columns in my proc report? I want the sum columns first for my across variable, then followed by percentage columns for my across variable.

 

This is my code so far and the output it produces:

proc report data=sashelp.shoes

(where=(region in ('Africa', 'Asia','Canada'))) nowd;

column Product Region, sales;

define product/group;

define region/across;

define sales/'sales in USD';

rbreak after/summarize;

run;

 

 

Region

   
 

Africa

Asia

Canada

Product

sales in USD

sales in USD

sales in USD

Boot

119835

62708

385613

Men's Casual

562794

11754

441903

Men's Dress

318500

119366

920101

Sandal

190409

8208

14798

Slipper

337076

152032

952751

Sport Shoe

22150

2092

140389

Women's Casual

417516

25837

410807

Women's Dress

374308

78234

989350

 

2342588

460231

4255712

 

But I want my report to give me a table like this:

 

Region

   

Region

   
 

Africa

Asia

Canada

Africa

Asia

Canada

Product

sales in USD

sales in USD

sales in USD

pct

pct

pct

Boot

119835

62708

385613

5 %

14 %

9 %

Men's Casual

562794

11754

441903

24 %

3 %

10 %

Men's Dress

318500

119366

920101

14 %

26 %

22 %

Sandal

190409

8208

14798

8 %

2 %

0 %

Slipper

337076

152032

952751

14 %

33 %

22 %

Sport Shoe

22150

2092

140389

1 %

0 %

3 %

Women's Casual

417516

25837

410807

18 %

6 %

10 %

Women's Dress

374308

78234

989350

16 %

17 %

23 %

 

2342588

460231

4255712

100 %

100 %

100 %

 

How can I do this in proc report? I'm using sas eg 7.13.

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Hi

 

With Proc REPORT, you have to do the percentage calculation yourself, see code below.

For a task like this one usually uses arrays, but they gave me some errors, so this code uses individual variable names.

 

In the COMPUTE BEFORE the column totals are put into a variable, this variable is then used in each row to calculate the percentage.

 

The reportItems _Cn_ refer to the column in the report.

 

proc report data=sashelp.shoes nowd;
  where region in ('Africa', 'Asia','Canada');
  column Product Region, sales region=region2, pctValue _dummy;
  define product/group;
  define region/across;
  define sales/'sales in USD';
  define region2 / across;
  define pctValue / computed format=percent9.2;
  define _dummy / computed noprint;
  rbreak after/summarize;

  compute before;
    salesTotal1 = _c2_;
    salesTotal2 = _c3_;
    salesTotal3 = _c4_;
  endComp;

  compute _dummy / char length=32;
    _c5_ = _c2_  / salesTotal1;
    _c6_ = _c3_  / salesTotal1;
    _c7_ = _c4_  / salesTotal1;
  endcomp;

run;

Bruno

View solution in original post

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Applying the format shoud do the trick:

proc report data=sashelp.shoes (where=(region in ('Africa', 'Asia','Canada'))) nowd;
  column Product Region, sales;
  define product/group;
  define region/across;
  define sales/'sales in USD' f=percent3.;
  rbreak after/summarize;
run;

I don't have anything to test this on though.

ak2
Calcite | Level 5 ak2
Calcite | Level 5

No. That does not give me what I want. I my output to give me both sums in amount and in percentages, like the table with 7 columns I posted.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

As I mentioned above, you have not posted any test data - in the form of a datastep - that I can test code, therefore I don't see how I can guess any further.

ak2
Calcite | Level 5 ak2
Calcite | Level 5
I'm using the data set sashelp.shoes. I thought it came with sas by default.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Oh yes, I see.  Here is a SAS sample which should cover what you want.  It uses computed columns/rows and internal variables:

http://support.sas.com/kb/43/091.html

ak2
Calcite | Level 5 ak2
Calcite | Level 5
No. It gives me the wrong order of the columns. I want my report to look like the table I posted that has 7 columns, and that first displays 3 columns with 'sales in USD' followed by 3 columns with percentages.
BrunoMueller
SAS Super FREQ

Hi

 

Proc TABULATE can calculate the percentage values in a simple way, see code example below. With Proc REPORT, there is more coding involved to do this. Does it have to be Proc REPORT? In SAS Enterprise Guide, you can use the Summary Tables task an use point and click to put together the report.

 


proc tabulate data=sashelp.shoes format=comma14.;
  where region in ('Africa', 'Asia','Canada');
  class product region;
  var sales;

  table
    product
    all
    ,
    region * sales
    region * sales * colpctsum
  ;

  table
    product
    all
    ,
    (region all)  * (sales*( sum colpctsum))
  ;
run;

Bruno

BrunoMueller
SAS Super FREQ

Hi

 

With Proc REPORT, you have to do the percentage calculation yourself, see code below.

For a task like this one usually uses arrays, but they gave me some errors, so this code uses individual variable names.

 

In the COMPUTE BEFORE the column totals are put into a variable, this variable is then used in each row to calculate the percentage.

 

The reportItems _Cn_ refer to the column in the report.

 

proc report data=sashelp.shoes nowd;
  where region in ('Africa', 'Asia','Canada');
  column Product Region, sales region=region2, pctValue _dummy;
  define product/group;
  define region/across;
  define sales/'sales in USD';
  define region2 / across;
  define pctValue / computed format=percent9.2;
  define _dummy / computed noprint;
  rbreak after/summarize;

  compute before;
    salesTotal1 = _c2_;
    salesTotal2 = _c3_;
    salesTotal3 = _c4_;
  endComp;

  compute _dummy / char length=32;
    _c5_ = _c2_  / salesTotal1;
    _c6_ = _c3_  / salesTotal1;
    _c7_ = _c4_  / salesTotal1;
  endcomp;

run;

Bruno

ak2
Calcite | Level 5 ak2
Calcite | Level 5
Thank you so much for your help! This is just what I needed. If it had to be a proc report? Not necessarily, but I would prefer it to be a report.
BrunoMueller
SAS Super FREQ

Hi

 

I just looked at some other example I did some time ago, and saw that Proc REPORT has support for PCTN and PCTSUM statistics.

 

So the code can be a lot easier, see example below. But it is always good to have an alternate way of doing things.

 

proc report data=sashelp.shoes nowd;
  where region in ('Africa', 'Asia','Canada');
  column Product Region, (sales sales=salespct) region=region2, sales=salesPct2 ;
  define product/group;
  define region/across;
  define sales/'sales in USD';
  define salespct/ pctsum 'in %' format=percent9.2;

  define region2 / across;
  define salespct2 / pctsum 'in %' format=percent9.2;

  rbreak after/summarize;

run;

Bruno

ak2
Calcite | Level 5 ak2
Calcite | Level 5

This code is indeed much easier, but it does not give me the columns in the order I needed them to be.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 11 replies
  • 10329 views
  • 1 like
  • 3 in conversation