Percent of Column (and row) using a Distinct count value

[ghartge]

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

[Sam_SAS · Accepted solution]

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]

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