Calculate Percent Change in VA

[ghartge]

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

[mleitson]

Hi Gary,

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

ML

[ghartge]

Hey ML,

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

Gary

[Robert_T]

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.

[ghartge]

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

[Robert_T]

I'm guessing that your variable 'Term Year'n is not formatted as a date with the year specified. The data item for the period calculation must be an item whose format specifies year.

http://support.sas.com/documentation/cdl/en/vaug/66720/HTML/default/viewer.htm#n1lxnqfip132can1hdzue...

[ghartge]

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

[kanchans]

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

[ccaulkins912]

!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

[ccaulkins912]

@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...