topic Re: Sorting Variables with Missing Values in SAS Procedures

Sorting Variables with Missing Values

Scottcom4 — Fri, 19 Jun 2009 06:46:13 GMT

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.

Re: Sorting Variables with Missing Values

deleted_user — Fri, 19 Jun 2009 08:27:50 GMT

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

Re: Sorting Variables with Missing Values

DanielSantos — Fri, 19 Jun 2009 09:10:36 GMT

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

Re: Sorting Variables with Missing Values

Scottcom4 — Sun, 21 Jun 2009 22:50:57 GMT

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

Greatly appreciated.