topic Re: Macro enable calculation? in SAS Programming

Macro enable calculation?

Jyuen204 — Tue, 05 Nov 2019 21:30:19 GMT

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.

Re: Macro enable calculation?

Tom — Tue, 05 Nov 2019 21:39:09 GMT

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.

Re: Macro enable calculation?

ballardw — Tue, 05 Nov 2019 21:51:06 GMT

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.

Re: Macro enable calculation?

Reeza — Tue, 05 Nov 2019 22:46:24 GMT

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

Re: Macro enable calculation?

PaigeMiller — Tue, 05 Nov 2019 22:57:08 GMT

@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.

Re: Macro enable calculation?

Kurt_Bremser — Wed, 06 Nov 2019 07:29:45 GMT

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.

(Maxim 19)

Re: Macro enable calculation?

Jyuen204 — Wed, 06 Nov 2019 15:59:42 GMT

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

Re: Macro enable calculation?

PaigeMiller — Wed, 06 Nov 2019 17:49:05 GMT

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


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