Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- SAS Viya
- /
- Visual Analytics
- /
- Calculate Percent Change in VA

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-25-2018 05:47 PM
(4843 views)

Greetings everyone!

Is it possible to calculate the percent change between to values in a SAS VA crosstab? I have an example attached which I could make more straightforward but the way the data is displayed is what was requested. I would like to calculate the percent difference between for example the value for 2017 Female Asian (36) and the 2016 Female Asian value (61) and all other values down the columns.

((36 - 61) / (61))*100 = 40.98%

The data driving this report is student level meaning one row per student which is grouped and totaled by SAS using distinct count on a dummy id text variable.

Thanks,

Gary

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Gary,

I've been trying to figure out a solution as well. Did you find a way?

ML

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hey ML,

No responses/solutions yet. Several views but it seems this isn't possible in VA.

Gary

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

You must create another aggregated measure using parallel period operator(s) along with some simple numeric operators.

Assuming that your existing "* Headcount*" aggregated measure is calculated as: Distinct [_ByGroup_] ('Row_ID'n)

Then your percent difference called "__ Headcount (% Diff)__" would be calculated as:

( ParallelPeriod(_DistinctCount_, 'Row_ID'n, _ApplyAllFilters_, 'Year'n, _Inferred_, _Inferred_, 0, _Full_, {Date}) - ParallelPeriod(_DistinctCount_, 'Row_ID'n, _ApplyAllFilters_, 'Year'n, _Inferred_, _Inferred_, -1, _Full_, {Date}) ) / Abs(ParallelPeriod(_DistinctCount_, 'Row_ID'n, _ApplyAllFilters_, 'Year'n, _Inferred_, _Inferred_, -1, _Full_, {Date}))

Set the format of this new aggregated measure to Percent, and drag it into the cross tab.

Hope this works for you.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you Robert_L,

Can you give me a bit of clarity on the 'Year'n value? I substituted the variables I use ('Term Year'n) and get the error(s) below. This is a character field.

( ParallelPeriod(_DistinctCount_, 'Student_ID'n, _ApplyAllFilters_, 'Term Year'n , _Inferred_, _Inferred_, 0, _Full_, {Date}) - ParallelPeriod(_DistinctCount_, 'Student_ID'n, _ApplyAllFilters_, 'Term Year'n, _Inferred_, _Inferred_, -1, _Full_, {Date}) )

/

Abs(ParallelPeriod(_DistinctCount_, 'Student_ID'n, _ApplyAllFilters_, 'Term Year'n, _Inferred_, _Inferred_, -1, _Full_, {Date}))

Invalid data item for this context: Term Year

Invalid data item for this context: Term Year

Invalid data item for this context: Term Year

Thank you,

Gary

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hey Robert_T (or anyone),

This "seems" WAY more complicated than it should be. In the attached image I describe what I am trying to do.

I have a category titled "Days From the 1st Day of Classes" that are my row values.

I have two semesters (Term_Year) that is a measure as columns. They are '2017' and '2018'.

I "simply" need to calculate the percent difference for each row.

2017 2018

Days From Headcount Headcount

42 15,576 15,587 *Need percent change here*

** as the third column**

The user doesn't want dates they need the number of days pre and post the first day of classes and the percent (and actual number) difference for each day. I cannot do this in EG since this data will be linked to a drop-down list for several different categories of students. The last day this runs there will be about 100 rows and the data will have interactions with a line graph and possibly a pie chart.

Days From the First Day of Classes - Category, Format Float

Headcount - Aggregated Measure - Distinct [_ByGroup_] ('Student_ID'n)

Term_Year - Category - Format $

Thanks!

Gary

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

hi,

I am stuck with exactly the same problem where I am trying to find % difference between the last two quarters of aggregated measure which I have calculated like this

Sum [_ByGroup_] ('twoplusatsix'n) / Sum [_ByGroup_] ('onbookatsix'n)

and for the year variable I have the date variable which has quarter and year in it

can someone please tell me how should I apply the formula for my aggregated measure to find the % difference

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

!mleit, #ghartg_e, et al. What @Robert_L said and linked to makes sense, if you're using the part of VA that he and others seem to think you are. @ccaulkins91, @ccaulkins9, #ccaulkins912

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@kanchans, If you were to re-post this as a new ticket you might be able to get some more feedback. Feel free to keep trying although it looks like you;'re no longer using SAS Communities? too bad...

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.