Using SUMs in calculated variable

[ilasadar]

Hello,

I want to create a calculated variable that returns the difference between a measure and it's grand total.

The formula for the variable would be:

Calculated variable = Measure - Sum[_ForAll_]

However, SAS VA does not allow aggregate variables to be used in creating calculated variables.

Is there a way the this can be achieved?

Thank you.

[Sam_SAS]

Hi ilasadar,

If your expression uses aggregated values, then you have to make an aggregated measure instead of a calculated measure.

An aggregated measure like this might do what you want, when applied to a visualization or report object:

Sum [_ForAll_] ('Expenses'n) - Sum [_ByGroup_] ('Expenses'n)

Does that help?

Thanks,

Sam

[Steelers_In_DC]

It would be better to give an example of what you have and a small dataset that provides an example of what you are looking for.  I think this is what you want, let me know if this helps:

data have;

infile cards;

input ID measure;

cards;

1 1

1 2

1 3

1 4

1 5

2 2

2 4

2 6

2 8

2 10

;

proc sql;

create table want as

select *,measure-sum_measure as calculated_value

from(

select *,sum(measure) as sum_measure

from have

group by id)

order by id;

[Steelers_In_DC]

I just looked at the output and it helps to complete the order by line:

proc sql;

create table want as

select *,measure-sum_measure as calculated_value

from(

select *,sum(measure) as sum_measure

from have

group by id)

order by id,measure;