topic How to add percentage columns in proc report in SAS Enterprise Guide

How to add percentage columns in proc report

ak2 — Fri, 10 Feb 2017 08:54:30 GMT

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.

Re: How to add percentage columns in proc report

RW9 — Fri, 10 Feb 2017 09:27:15 GMT

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.

Re: How to add percentage columns in proc report

ak2 — Fri, 10 Feb 2017 09:39:52 GMT

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.

Re: How to add percentage columns in proc report

RW9 — Fri, 10 Feb 2017 10:00:20 GMT

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.

Re: How to add percentage columns in proc report

ak2 — Fri, 10 Feb 2017 10:36:20 GMT

I'm using the data set sashelp.shoes. I thought it came with sas by default.

Re: How to add percentage columns in proc report

RW9 — Fri, 10 Feb 2017 10:56:43 GMT

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

Re: How to add percentage columns in proc report

ak2 — Fri, 10 Feb 2017 11:01:14 GMT

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.

Re: How to add percentage columns in proc report

BrunoMueller — Fri, 10 Feb 2017 14:20:27 GMT

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

Re: How to add percentage columns in proc report

BrunoMueller — Fri, 10 Feb 2017 14:47:52 GMT

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

Re: How to add percentage columns in proc report

ak2 — Mon, 13 Feb 2017 07:23:50 GMT

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.

Re: How to add percentage columns in proc report

BrunoMueller — Mon, 13 Feb 2017 08:13:55 GMT

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

Re: How to add percentage columns in proc report

ak2 — Mon, 13 Feb 2017 08:22:05 GMT

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