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.
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
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.
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.
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.
Oh yes, I see. Here is a SAS sample which should cover what you want. It uses computed columns/rows and internal variables:
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
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
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
This code is indeed much easier, but it does not give me the columns in the order I needed them to be.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.