Macro enable calculation?

[Jyuen204]

I have a macro that has tabulated data into multiple categories

Metric            Period1           Period2           Period 3

Value1           3                     5                      4

Value2           4                     6                      2

Value3           2                     3                      2
Total              9                     14                    8

now I want to be able to auto calculate the percentage of the Value metrics over the totals
So I want to return something like this.

Metric            Period1           Period2           Period 3

Value1           .333                     .357                     .5

Value2           .444                     .429                     .25

Value3           .222                     .214                     .25

Trying to wrap my head around it and I haven't been able to make this work. Any assistance/guidance would be greatly appreciated. Thank you.

[Tom]

Before you start trying to create a macro to generate the code first design the code that you want to generate.

So is that first table supposed to be an example of the dataset that you have?  So you have the totals in an extra observation in the dataset?

Or are those just examples of the types of REPORTs that you would like to generate?  In that case then please show what the actual data looks like.

[ballardw]

It may help to post the macro code that "tabulated" those values to begin with.

If you were actually putting calculated values into macro variables then likely way too much work was involved for what should be a simple Proc Freq or Proc Report.

[Reeza]

Those are standard proc freq outputs. Redesign your macro to use proc freq and output both the column percents and counts as needed.

[PaigeMiller]

---

@Jyuen204 wrote:

I have a macro that has tabulated data into multiple categories

Metric            Period1           Period2           Period 3

Value1           3                     5                      4

Value2           4                     6                      2

Value3           2                     3                      2
Total              9                     14                    8

now I want to be able to auto calculate the percentage of the Value metrics over the totals
So I want to return something like this.

Metric            Period1           Period2           Period 3

Value1           .333                     .357                     .5

Value2           .444                     .429                     .25

Value3           .222                     .214                     .25

Trying to wrap my head around it and I haven't been able to make this work. Any assistance/guidance would be greatly appreciated. Thank you.

---

No macro needed

data want;
    if _n_=1 then set have(where=(metric='Total')
        rename=(period1=period1t period2=period2t period3=period3t));
    set have(where=(metric^='Total'));
    period1=period1/period1t;
    period2=period2/period2t;
    period3=period3/period3t;
run;

Macros are an additional complication that provide little or no benefit in this situation, avoid them in this situation.

[Jyuen204]

Obviously this is just a sample layout. my table generated has 6 columns of data, and many more rows, including a TOTAL row that i summed for all of the metrics i have aggregated.

Name: TABLE_X

Not sure how to reference the table in the code you had provided.
My apologies I am pretty green at all of this.

Thank you

[PaigeMiller]

Get rid of the total row you generated, and do the following

proc freq data=have;
    tables metric*(pm p2m p3m p4m p5m);
run;

[Kurt_Bremser]

As soon as you get your data in shape (Maxim 33), it's a simple SQL:

data have;
input Metric $ Period1 Period2 Period3;
datalines;
Value1           3                     5                      4
Value2           4                     6                      2
Value3           2                     3                      2
;

proc transpose data=have out=trans(rename=(_name_=period col1=value));
by metric;
var period:;
run;

proc sql;
create table want as
select
  metric, period, value, value / sum(value) as percentage
from trans
group by period
order by metric, period;
run;

Long beats wide.

Long beats wide.

Long beats wide.

(Maxim 19)