Desktop productivity for business analysts and programmers

How to add percentage columns in proc report

Accepted Solution Solved
Reply
Contributor ak2
Contributor
Posts: 27
Accepted Solution

How to add percentage columns in proc report

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.


Accepted Solutions
Solution
‎02-13-2017 02:21 AM
SAS Super FREQ
Posts: 676

Re: How to add percentage columns in proc report

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


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: How to add percentage columns in proc report

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.

Contributor ak2
Contributor
Posts: 27

Re: How to add percentage columns in proc report

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: How to add percentage columns in proc report

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.

Contributor ak2
Contributor
Posts: 27

Re: How to add percentage columns in proc report

I'm using the data set sashelp.shoes. I thought it came with sas by default.
Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: How to add percentage columns in proc report

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

Contributor ak2
Contributor
Posts: 27

Re: How to add percentage columns in proc report

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.
SAS Super FREQ
Posts: 676

Re: How to add percentage columns in proc report

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

Solution
‎02-13-2017 02:21 AM
SAS Super FREQ
Posts: 676

Re: How to add percentage columns in proc report

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

Contributor ak2
Contributor
Posts: 27

Re: How to add percentage columns in proc report

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.
SAS Super FREQ
Posts: 676

Re: How to add percentage columns in proc report

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

Contributor ak2
Contributor
Posts: 27

Re: How to add percentage columns in proc report

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 243 views
  • 0 likes
  • 3 in conversation