topic Re: How to calculate ratio between two variables in SAS Visual Analytics

How to calculate ratio between two variables

ghartge — Wed, 11 Apr 2018 14:08:41 GMT

Greetings everyone,

OK, I need some help...again. I have a dataset that contains course enrollment information. The data has various attributes for a course, such as division, department, program, section, seats allocated, seats filled (enrollment), campus, etc. I would like to have the ratio of enrollment to seats filled done in VA due to the various selections a user may require.

I can calculate the ratio of each course, which I have done in EG, to the level of the most attributes, i.e.

Division, Department, program, campus location, time of day, section, course.

The problem is when I need to display a more global summary, say only to the course by itself

(Division, Department, Program, COURSE

I can only get the summation of the ratios in the data, not simply the ratio of the displayed values of enrollment and seats allocated.

I attached an image I hope helps clarify what I am trying to accomplish.

I am thinking this isn't possible in SAS VA 7.3.

Thanks,

Gary

Re: How to calculate ratio between two variables

shantanupl1 — Wed, 11 Apr 2018 14:27:24 GMT

Try this code:

proc sql;
create table want as
select distinct course, sum(seat_allocated) as seat_allocated, sum(enrollment) as enrollment,
       (calculated seat_allocated/calculated enrollment) as fill_ratio from have group by course;
quit;

Re: How to calculate ratio between two variables

ghartge — Wed, 11 Apr 2018 14:53:23 GMT

Thanks, shantanupl1,

Yes, I can do that (and have) in my EG program that generates the data for my report. I am trying to accomplish the same inside of SAS VA using a Crosstab. I am unable to find a way to display a ratio as you described using VA when a user wishes to see the values at the course level only. VA will sum the ratio values and display a total of those values so we have ratios of 13.69 instead of .933 (or 93.3%) for example.

Gary

Re: How to calculate ratio between two variables

ghartge — Wed, 11 Apr 2018 17:34:08 GMT

I solved the issue. I know I attempted this prior to my question but perhaps I had focus on a different area than I needed to be. I don't know but you simply create an "Aggregated Measure"

Sum [_ByGroup_] ('Enrollment'n) / Sum [_ByGroup_] ('Seats Allocated'n)