Complex aggregate calculation help

silversky9 — Thu, 21 Jul 2022 22:06:48 GMT

Hi,

I'm using Visual Analytics 8.5. My data set consists of student credit hour group, race, gender, student headcount, semester etc. I have three student credit hour groups (categorical variable): 16+, 15, 12-14. Previously, I only was tasked to look at the 16+ credit hour group. The question was to determine the percent of students taking 16+ credit hours by race/gender by semester, and this was displayed in a line graph. To achieve this, in effect I created new variables for each race group, new variables for each gender group, and then new variables for each gender/race combination. For example, if I wanted to know the % of Hispanic students, I used the following aggregate calculation:

Sum [_ByGroup_] ('TTLHispanic16+'n) / AggregateTable(_Sum_,
Table(_Sum_, Fixed('Semester'n), 'TTLHispanic'n))

I created the variable TTLHispanic16+ using the following:

IF ( ( 'Credit Hour Group'n = '16+' ) AND ( 'race'n= 'Hispanic' ) )
RETURN 'Headcount'n
ELSE 0

The variable TTLHispanic was calculated similar to above. This method was not ideal, but it worked as I only was looking at one of the three credit hour groups. Now since I'm trying to use two more credit hour groups in my graph, it will look very messy to have multiple gender/race groups by credit hour group. I would like to be able to get the % of each credit hour group by race (or gender, or race/gender) by semester, and this will be used in a line graph. Ideally, each of these variables would be in a filter which the end user can select from.

For example, for Fall 2021, the percent of (16+ credit hours Hispanic/Total Hispanic) = (478/2701) = 17.7%. I've attached sample data to illustrate where I got this value.

Can someone help with this calculation? (% of each credit hour group by race, etc. by semester)

Thank you.

Re: Complex aggregate calculation help

acordes — Mon, 25 Jul 2022 08:48:14 GMT

Use table aggregator.

You have to convert date into character variable by using the 'format' function.

Then construct the crossings you need.

AggregateTable(_Sum_, Table(_Sum_, Fixed('Repairing Region'n,
'_in-service-date'n, 'Engine Model'n), 'Frequency'n)) /
AggregateTable(_Sum_, Table(_Sum_, Fixed('Repairing Region'n,
'_in-service-date'n), 'Frequency'n))

Re: Complex aggregate calculation help

silversky9 — Tue, 26 Jul 2022 17:10:59 GMT

Thanks so much, I was able to get what I needed. I appreciate the help!

topic Re: Complex aggregate calculation help in SAS Visual Analytics

Complex aggregate calculation help

Re: Complex aggregate calculation help

Re: Complex aggregate calculation help