Help using Base SAS procedures

Sorting Variables with Missing Values

Reply
Contributor
Posts: 42

Sorting Variables with Missing Values

Hi All,

I am trying to sort using SQL (as I am reading the data in from an oracle database and this appears to be the most efficient method), however it sorts blanks last. As a result I get:

1
2
.
.
.


Where as with Proc Sort I get:

.
.
.
1
2

Does anyone know a way to have Proc SQL sort in the same way that Proc Sort does?

Thank you for your help.
N/A
Posts: 0

Re: Sorting Variables with Missing Values

Posted in reply to Scottcom4
Hi,

Please try this

data x;
input a;
cards;
1
2
.
.
.
;
run;

proc sql;
create table x1 as
select *
from x
order by a;
quit;

Rgds,
skm
Super Contributor
Posts: 474

Re: Sorting Variables with Missing Values

Posted in reply to Scottcom4
Trouble is MISSING/NULLS have different meanings for ORACLE and SAS.

When you perform an Order by through SQL Pass-Through (on the ORACLE side), it seems NULLS are considered a very big number.
When you perform a SAS SORT, MISSINGS are considered a very little number.

Have you tried to order through a COALESCE function?

ORDER BY COALESCE (COL, -1)

The COALESCE function causes any results that are NULL to be replaced by some other value you wish (in the example, -1).

Not a pretty solution, but a easy one that may do what you whish.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
Contributor
Posts: 42

Re: Sorting Variables with Missing Values

Posted in reply to DanielSantos
Thank you so much Daniel. Your explanation was very clear and everything worked perfectly.

Greatly appreciated.
Ask a Question
Discussion stats
  • 3 replies
  • 93 views
  • 0 likes
  • 3 in conversation