Difference between the overall % of a population and the filtered %

[ghartge]

Greetings,

I have been asked to produce a SAS VA 7.5 report that will give the user the following;

The overall population of a group broken down by race/ethnicity

The percentage breakdown by race/ethnicity of the same group filtered for any number of dropdown boxes, as subset of the first group.

The difference between those values.

For example,

Ethnicity	Overall Population	Filtered Overall population	Difference
Group 1	21.0%	23.50%	-2.5%
Group 2	14.3%	14.60%	-0.3%
Group 3	4.4%	4.70%	-0.3%
Group 4	54.0%	51.80%	2.2%
Group 5	6.3%	5.40%	0.9%

I can create crosstabs, lists, tables, etc. that contain each of these breakdowns, but am at a loss as to how to calculate the difference between the two.

I thought about using parameters on a table or other object, but that only given me the character values, not the percentages, or that method isn't possible.

Thank you for any help. I cannot address it in my SAS EG programming since the combination of filters that can be used is huge.

Gary

[JustinCase]

Hi Gary @ghartge 

The assignment worked for me by creating a character parameter that is populated by a drop down list and a calculated measure item that has the measure for only your subgroup.

My dataset contains sales data of a retail chain. It has the variables Shop Province and Customer Province and a measure named Total Sales.

If I want to imitate your case I want to see the difference in Total sales by Shop Province overall and per selected Customer province.

Step 1: Create a Total Sales Customer Province Parameter that is Classified as category and has no default value (or any default value you choose) and allows only 1 value.

Step 2: Insert a drop downlist onto your canvas and add Customer Province to the Category role and the parameter of step 1 to the Parameter role.

Step 3: Create the measure for your selection: 

IF ( 'Customer Province'n = 'Total Sales Customer Province'p )
RETURN 'Total Sales'n
ELSE 0

 And name it Total Sales selection

Step 4: Create aggregated measures Total Sales (%) and Total Sales selection (%) by right clicking on Total Sales and then choose New calculation. Choose Percent of Total - Sum

Step 5: Create an aggregated measure Difference in Total sales percentage with expression

'Total Sales (%)'n - 'Total Sales selection (%)'n

Step 6: Drag a list table onto your canvas and add the variables Shop Province, Total Sales (%), Total Sales selection (%) and Difference in Total sales percentage. My result:

If you choose Ethnicity in stead of Shop Province and Population in stead of Total Sales and your selection variable in stead of Customer Province then you are there.

Kees Kramer

[ghartge]

Thank you Kees! I will see if I can use your approach. Looks pretty straight forward.