Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- proc sql: order by and outer union corr

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-09-2014 08:21 PM

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.

Accepted Solutions

Solution

04-09-2014
08:55 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gzr2mz39

04-09-2014 08:55 PM

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**;

All Replies

Solution

04-09-2014
08:55 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gzr2mz39

04-09-2014 08:55 PM

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**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-09-2014 11:16 PM

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

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

04-10-2014 08:12 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jwillis

04-10-2014 10:19 AM

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

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

04-10-2014 11:44 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jwillis

04-10-2014 12:26 PM

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.