Sorting Variables with Missing Values

[Scottcom4]

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.

[deleted_user]

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

[DanielSantos]

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

[Scottcom4]

Thank you so much Daniel. Your explanation was very clear and everything worked perfectly.

Greatly appreciated.