BookmarkSubscribeRSS Feed
ghartge
Quartz | Level 8

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,

 

EthnicityOverall PopulationFiltered Overall populationDifference
Group 121.0%23.50%-2.5%
Group 214.3%14.60%-0.3%
Group 34.4%4.70%-0.3%
Group 454.0%51.80%2.2%
Group 56.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

2 REPLIES 2
JustinCase
Fluorite | Level 6

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.

JustinCase_0-1623741394654.png

 

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

JustinCase_2-1623741849167.png

 

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:

JustinCase_3-1623742148810.png

 

 

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
Quartz | Level 8

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 543 views
  • 4 likes
  • 2 in conversation