BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ghartge
Quartz | Level 8

Greetings everyone,

 

First, I apologize if this has been posted before, but any search I do only brings up information for SAS VA 7.4 or below or Viya.

 

We just upgraded to SAS VA 7.5 this week and I have been asked to add a percent of the total to an existing report. The report is student enrollment by full-time and part-time, ethnicity and gender. The data that drives this report is a table of all coursework students have taken for a given term and year so student IDs are duplicated due to students taking multiple classes. I could create a new table but that is additional space on the LASR server for data that actually already exists. 

 

I use the Distinct [_ByGroup_] ('Student_ID'n) aggregated measure to display unduplicated headcounts for the various fields in the report. I have included an image of the report.

 

Right clicking on the column heading for gender or headcount does not give me the option to create a percent of row or column. If I create a calculated item for each gender and place that in the crosstab I do see the option for creating the percent values I need, but the percent values are then based on duplicated student IDs not the unique/unduplicated values in my aggregated measure Headcount value.

 

Is there a way I can create percent of totals on the unduplicated student ID count?

 

I did try this but it isn't valid;

 

IF ( 'Student Gender'n Contains 'Female' )
RETURN Distinct [_ByGroup_] ('Student_ID'n)
ELSE 0


Thank you and I do apologize if this has been answered.

 

Gary

1 ACCEPTED SOLUTION

Accepted Solutions
Sam_SAS
SAS Employee

Hi Gary,

 

You can create Percent of Total only when the measure uses the Sum or Count aggregations. With Distinct, it is not available.

 

Your best approach is probably to create a copy of the table where there is only one row for each student ID, and use that when you do not need the course data.

 

Sam

View solution in original post

2 REPLIES 2
Sam_SAS
SAS Employee

Hi Gary,

 

You can create Percent of Total only when the measure uses the Sum or Count aggregations. With Distinct, it is not available.

 

Your best approach is probably to create a copy of the table where there is only one row for each student ID, and use that when you do not need the course data.

 

Sam

ghartge
Quartz | Level 8

Thank you Sam!

 

I understand. What I ended up doing was to add a column in my data based on the hierarchy of my report; Year, Term, Full/Part Time, Student ID. This identifies the first instance of a student with those attributes by marking it with the number 1. All other instances of the same Year, Term, Full/Part Time, Student ID were identified as a 0 for that student. This gave me a measure of distinct count that I could use for column percentages.

 

This appears to be working great as long as the hierarchy remains the same and keeps me from using more space on the LASR server.

 

Thanks again!

 

Gary

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
  • 1894 views
  • 1 like
  • 2 in conversation