proc sql: order by and outer union corr

[gzr2mz39]

An error is being generated in between the order by and outer union corr lines.

I'm trying to sort dart_tot in descending order and then have 'Total' dart_tot appear on the last row.

Does anyone know how to fix this?

proc sql;

CREATE TABLE loc_sum as

SELECT loc_grp,

     sum(dart_new) as dart_tot

FROM new_file1

where dart_new=1

group by loc_grp

order by dart_tot desc

outer union corr

select 'Total' as loc_grp,

     sum(dart_new) as dart_tot

from new_file1

;

QUIT;

RUN;

Thank you.

[Tom · Accepted solution]

The ORDER BY clause must be last.

If you want to order the detail rows separately from the total row then you should add another grouping variable that you can use to order.

Also did you mean to only use the WHERE clause on the detail lines?

proc sql;

create table loc_sum as

    select 0 as sort_grp

       , loc_grp

       , sum(dart_new) as dart_tot

   from new_file1

   where dart_new=1

   group by loc_grp

outer union corr

   select 1 as sort_grp

       , 'total' as loc_grp

       , sum(dart_new) as dart_tot

   from new_file1

   order by sort_grp

        , dart_tot desc

;

quit;

[PGStats]

Or, since OUTER UNION CORR keeps non-matching columns, you may let loc_grp be missing on the total line and put the 'total' label in a new sorting variable, like this:

proc sql;

create table loc_sum as

     select

          loc_grp,

          sum(dart_new) as dart_tot

     from new_file1

     where dart_new=1

     group by loc_grp

outer union corr

     select

          'total' as sort_grp,

          sum(dart_new) as dart_tot

     from new_file1

/* where dart_new=1 */

     order by

          sort_grp,

          dart_tot desc

;

quit;

Note: If you want to see the cases where dart_tot=0, you should use sum(dart_new=1) as dart_tot and remove the where clause.

PG

[jwillis]

PG STATS,

I have never seen the convention function(argument equals value)  before.  What is the effect of  "sum(dart_new equals 1)" on each row of the table produced by the sql?

[PGStats]

In SAS-SQL expressions, the result of a comparison operator is 1 (=TRUE) or 0 (=FALSE). Thus sum(dart_new=1) counts the number of cases where dart_new = 1. - PG

[jwillis]

PG,

Please forgive my ignorance.  I am trying to understand a neat concept.  Your note and what the SAS book says are confusing me.  I understand  that any posiitve number greater than zero is "true" and zero and negative numbers are "false".  I do not understand how "sum(dart_new=1)" is equivalent to "where dart_new=1".  If I understand the SQL code above dart_new is a variable containing a numeric value of 1 or zero and the sum(dart_new) statement in the unioned query will sum all the non-missing values of dart_new.

Note: If you want to see the cases where dart_tot=0, you should use sum(dart_new=1) as dart_tot and remove the where clause.

SUM Function (SAS 9.3 Functions and Call Routines Reference Volume 2. Page 877

"Returns the sum of the nonmissing arguments".

[Tom]

You are mostly there.

SAS interprets non-missing values other than 0 as TRUE.

What you also need to know is that when it evaluates a logic expression, such as (dart_new=1), it will return 1 when true and 0 otherwise.

So if you code SUM( (dart_new=1) ) in SQL statement then you are asking to sum the results of that logic expression. So basically you are counting the number of rows where dart_new=1.