Calulate percentage values in a table

[Anita_n]

Hi all,

if I have a table like this

data have;
input var1  $3.  var2  var3;
datalines;
A  24    15
B  44    25
C  55    66
D  19    11
sum 142 117
;
run;

and I want to calculate the percentages for each row/column

like for row A will be 

A%= 24/142*100

and =15/117*100

and that repeated for each row. So that at the end I have a result that looks like this:

var1 var2 var3 

A 24 15

A%    16.90%   12.82%

B 44 25

B%   31%    21,37%

C 55 66

c%    38.73% 54.41%

D 19 11

D%  13.38%  9.40%

sum 142 117

sum%  100.00% 100.00%

Any help?

[PaigeMiller]

Here is some partial code. The place where I have put the comment "Compute Percents" is left as a homework assignment for you.

data want;
    if _n_=1 then set have(where=(var1='sum') rename=(var2=var2_total var3=var3_total));
    set have;
    /* Compute percents */
run;

[ballardw · Accepted solution]

By "table" do you mean a SAS data set or a report that people read?

I think that you should seriously reconsider if this is to be a data set because you are implying the percents should be in the same variable as counts and that is going to make a data set nearly impossible to work with and shows a great deal of trying to force SAS to look like a spreadsheet.

A report is pretty easy, at least for the shown values, if you don't provide summaries in the data:

data have;
input var1  $3.  var2  var3;
datalines;
A  24    15
B  44    25
C  55    66
D  19    11
;
run;

proc tabulate data=have;
   class var1;
   var var2 var3;
   table (var1 all='Sum')*(sum=' ' *f=best. colpctsum='%' ),
         var2 var3
         /row=float
   ;
run;

[Anita_n]

@ballardw I was tring to also sum up the rows but can get the syntax right.

I want to have something like a var_total next to var3 which has the total of each row and the percentages below. I just can get the proc tabulate syntax right

[PaigeMiller]

I guess all of the above is much easier done in Excel, probably wouldn't even take 5 minutes.

Programming these very specific table layouts will probably take longer, and requires some expertise in PROC TABULATE or PROC REPORT.

[ballardw]

---

@Anita_n wrote:

@ballardw I was tring to also sum up the rows but can get the syntax right.

I want to have something like a var_total next to var3 which has the total of each row and the percentages below. I just can get the proc tabulate syntax right

---

1) then why did you not show that in the first place.

2) the data would need some reshaping to make a "nice" report especially with Proc Tabulate. Tabulate only does statistics for single variables in a dimension so to sum (or anything) for Var2 and Var3 you need to create a single variable with the value to sum and additional variables to use to indicate which variable held the initial value.

data have;
input var1  $3.  var2  var3;
datalines;
A  24    15
B  44    25
C  55    66
D  19    11
;
run;

proc transpose data=have
   out=trans prefix=value;
  by var1;
  var var2 var3;
run;

proc tabulate data=trans;
   class var1 _name_;
   var value1;
   table (var1 all='Sum')*value1=' '*(sum=' ' *f=best. colpctsum='%' ),
         _name_=' ' All='Total'
         /row=float
   ;
run;

Proc Report will allow you to do row "sums" of variables but 1) the syntax is clunky, 2) not dynamic and 3) really does not like to stack different statistics (sum and %) in a single column.

Note: the transpose step above requires sorting the data if your Var1 values are not already sorted and if your data is more complex than initially shown may not provide what you need with multiple rows of Var1 values. Which could require different approaches depending on the actual data.

[Anita_n]

okay, I will try this to see, if I can solve it. thanks