topic Re: SQL Query to Expression Conversion in SAS Web Report Studio

SQL Query to Expression Conversion

DavidPhillips2 — Tue, 28 Jun 2016 18:17:35 GMT

select sum(hs_GPA)/sum(enrolled) from mytalbe

where ADMISSIONS_POPULATION = 'Enrolled' and enrolled >0 and hs_gpa is not null;

The closest thing I have is

case when (<<Measures.HS_GPASUM_for Calc>> is null) then null else sum(<<Measures.HS_GPASUM_for Calc>>)/suM(<<Measures.ENROLLEDSUM>>) end

Then I set a filter on where enrolled.

However, the result comes out to .2 rather than an expected GPA. Its records with null GPA are still being calculated.

Re: SQL Query to Expression Conversion

Reeza — Tue, 28 Jun 2016 18:44:39 GMT

I'm confused, what are you trying to convert to an expression?

Re: SQL Query to Expression Conversion

DavidPhillips2 — Tue, 28 Jun 2016 18:45:39 GMT

select sum(hs_GPA)/sum(enrolled) from mytable

where ADMISSIONS_POPULATION = 'Enrolled' and enrolled >0 and hs_gpa is not null;

Re: SQL Query to Expression Conversion

Reeza — Tue, 28 Jun 2016 19:19:10 GMT

Ok, but why convert, why not leave as is?

This may be an alternative:

sum(hs_GPA*(admissions_population='Enrolled' and enrolled>0 and hs_gpa is not null))/sum(enrolled*(admissions_population='Enrolled' and enrolled>0 and hs_gpa is not null))

Re: SQL Query to Expression Conversion

DavidPhillips2 — Tue, 28 Jun 2016 19:24:34 GMT

I'm confused. If I need to access the dimension in Web Report Studio don't I need to convert it to an expression to use a custom data item?

Re: SQL Query to Expression Conversion

Reeza — Tue, 28 Jun 2016 19:54:46 GMT

My bad. Missed the WRS portion. I have no idea about it.