topic Re: proc sql: order by and outer union corr in SAS Procedures

proc sql: order by and outer union corr

gzr2mz39 — Thu, 10 Apr 2014 00:21:29 GMT

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.

Re: proc sql: order by and outer union corr

Tom — Thu, 10 Apr 2014 00:55:33 GMT

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;

Re: proc sql: order by and outer union corr

PGStats — Thu, 10 Apr 2014 03:16:26 GMT

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.

Re: proc sql: order by and outer union corr

jwillis — Thu, 10 Apr 2014 12:12:03 GMT

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?

Re: proc sql: order by and outer union corr

PGStats — Thu, 10 Apr 2014 14:19:46 GMT

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

Re: proc sql: order by and outer union corr

jwillis — Thu, 10 Apr 2014 15:44:47 GMT

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".

Re: proc sql: order by and outer union corr

Tom — Thu, 10 Apr 2014 16:26:20 GMT

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.