Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

How to calculate ratio between two variables

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

How to calculate ratio between two variables

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


Accepted Solutions
Solution
‎04-11-2018 01:34 PM
Contributor
Posts: 34

Re: How to calculate ratio between two variables

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)

 

 

Aggrgated_Measure.jpg

View solution in original post


All Replies
Occasional Contributor
Posts: 5

Re: How to calculate ratio between two variables

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;

 

Contributor
Posts: 34

Re: How to calculate ratio between two variables

Posted in reply to shantanupl1

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

Solution
‎04-11-2018 01:34 PM
Contributor
Posts: 34

Re: How to calculate ratio between two variables

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)

 

 

Aggrgated_Measure.jpg

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 257 views
  • 0 likes
  • 2 in conversation