Measure per 10000 people

[LukeL]

Hi all.
I'm pretty sure it will not possible to do, but I hope there is some nice truck I do not know to perform this...
I have a dataset containing lines as
City1 District1 Sex1 Event1 Class1 Measure
City1 District2 Sex1 Event2 Class1 Measure
City1 District1 Sex2 Event1 Class1 Measure
CityN DistrictN Sex1 Event2 Class2 Measure
And so on...
What I need to display in VA is measure per (10000) people.
Clearly, people depend on City district and sex, but not on event and class. There are lots more variables not changing population, and all are used as possible filters.
Is there a way to dinamically create the per people measure dinamically?
Ie, the population is
City1 District1 Sex1 Pop1
City1 District1 Sex2 Pop2
City1 District2 Sex1 Pop3
City1 District2 Sex2 Pop4
....

So if I'm looking the city events the have to be over pop1+pop2+pop3+pop4, if I'm also on sex, pop1+pop3 and pop2+pop4... And so on...


Hope the explanation is clear... 😅
Thanks anyone

[Reeza]

Couple of options depending on exactly what you want.

1. Convert the units to be aggregated ahead of time but pop1/10000+pop2/10000 = (pop1 + pop2)/10000 so scaling the individual values ahead of time will work, unless you're standardizing the events over the population/another variable (not clear).
2. Have SAS display values in tens of thousands using a custom format

[LukeL]

Ok, I think I may not have expressed myself clearly.

I'd try to make a better example for my problem...

Suppose you have these two tables of event and population:

data TEST;
    length CITY DISTRICT EVENT $5 QTY 8;
    input CITY DISTRICT EVENT QTY;

datalines;
City1 Dist1 Even1 2
City1 Dist1 Even2 4
City1 Dist1 Even3 6
City1 Dist1 Even4 4
City1 Dist2 Even1 1
City1 Dist1 Even1 2
City1 Dist1 Even1 4
City2 Dist1 Even4 15
City2 Dist1 Even1 7
City2 Dist2 Even1 9
;;
run;

data POPULATION;
    length CITY DISTRICT $5 POP 8;
    input CITY DISTRICT POP;

datalines;
City1 Dist1 100
City1 Dist2 123
City2 Dist1 45
City2 Dist2 96
;;
run;

Now on VA I have a cross table with a City > District hierarchy and a filter on events.

What I need is this:

No event filter active:

City   Qty   Pop    Rate
City1  23    223    23/223x10000
City2  31    141    31/141x10000

No event filter active but hierarchy open:

City   District    Qty   Pop    Rate
City1  Dist1       22    100    22/100x10000
City1  Dist2       1     123    1/123x10000
City2  Dist1       22    45     22/45x10000
City2  Dist2       9     96     9/96x10000

But if I activate a filter on an event, let's say event1, I need these output:

City   Qty   Pop    Rate
City1  9     223    9/223x10000
City2  16    141    16/141x10000

City   District    Qty   Pop    Rate
City1  Dist1       8     100    8/100x10000
City1  Dist2       1     123    1/123x10000
City2  Dist1       7     45     7/45x10000
City2  Dist2       9     96     9/96x10000

So, basically, the Qty depends on the event variable, while the population depends only from City and District variabiles.

[LukeL]

Ups, never mention... We are on VA 7.5

[Reeza]

Maybe I'm not understanding your question. Mathematically, if you need to multiply everything by 10000 you can do that to one of the columns then it flows through the rest. So if you create a new variable QTY_scaled = QTY*10000 and then do the calculations as QTY_scaled / POP it'll show as expected.

Or are you having issues getting the aggregations to aggregate properly? If you scale QTY, it seems like the aggregations also scale.

[LukeL]

The problem is not on the per 100.000 part, but for the aggregation... I need the population only aggregate through city and district, but not for eample by event or other variables. If I put the population on the main dataset, I will have pop be summed for each event, giving completly wrong results. On the other hand, if I put the population on another dataset, as far as I know, I will not be able to construct the measure using measures from different data sources

[Reeza]

If I put the population on the main dataset, I will have pop be summed for each event, giving completly wrong results.

What do you mean by this?

[LukeL]

Let's say I have these data:

City  Dist  Event Qty     Pop
City1 Dist1 Even1 2       100
City1 Dist1 Even2 4       100
City1 Dist1 Even3 6       100
City1 Dist1 Even4 4       100
City1 Dist2 Even1 1       150
City1 Dist1 Even1 2       100
City1 Dist1 Even1 4       100
City2 Dist1 Even4 15      200
City2 Dist1 Even1 7       200
City2 Dist2 Even1 9       250

If I compute the rate by row, I obtain the correct measure. But if I want to aggregate by city, for city1 ie I will obtain Qty=2+4+6+4+2+1+4=23 but I will also obtain Pop=100+100+100+100+150+100+100=750, that's wrong, since the right population is just 100+150=250.

[Reeza]

Random check:
Is this the difference of the following that you're looking for:
Define your metric as SUM(EVENT)/SUM(POPULATION)*10000 not SUM(EVENT/POPULATION)*10000

[LukeL]

It would be more somenthing like sum [by event] (QTY) / sum [by district] (POP) * 10000

[Reeza]

Unfortunately I don't know the answer to that one 😞

[acordes]

Yesterday I had to overcome a similar challenge. 

here comes a recipe for solving it.

https://blogs.sas.com/content/sgf/2018/09/05/selective-filtering-in-sas-visual-analytics-8-2/ 

[LukeL]

Thank you for pointing me that out... It is really interesting, but if I understand well, it works just with string based measures (count/count distinct/...) and not with numeric summable ones...

[acordes]

No, it works for both cases.
You end up having new variables that underlay different filters.
Selected versus all for example.
That can't be achieved with normal filters but with parameters.

[Reeza]

It is 8.2 though, so not sure it's available in 7.5