Proc SQL: order by nulls last

[pichro]

Is it possible to order missing values at the end (SAS 9.1.3)?

Some SQL-Standards support "order by nulls last". Is there a similar feature in Proc SQL or maybe in Proc Sort?

Default Order (by year):
. 407
2008 123
2009 50
2010 234

Desired Order (by year nulls last)
2008 123
2009 50
2010 234
. 407

[deleted_user]

hello,

you can use proc format in combination with one data step and one proc sort:

data test;
input year amt;
datalines;
. 407
2008 123
2009 50
2010 234
;

proc format;
invalue change
.=9999
other=_same_;
run;

data int;
set test;
s=input(put(year,best12.-l),change.);
run;

proc sort data=int out=test_sort(drop=s);
by s;
run;

Marius

[pichro]

Hello and thanks for the hint. It works well for the example, but i'm searching for a more general solution working for all datatypes and value ranges.

[ballardw]

I'd suggest selecting for the non-nulls and nulls separately and using a UNION with the nulls in the second part of the clause.

[sivaji]

Check this will work.

Proc sql;
select col1,col2 from table
order by (case when col1 is null then 999999 else col1 end) , col2;
Quit;

SD

[pichro]

The Problem is, that all solutions need information about the data.

I'm searching for a general solution, which is not depending on data or datatype, and works for every dataset, like a standard SQL-Syntax or a SQL-Option. In Oracle-SQL there is a "order by nulls last", which does exactly the job i'm serching for. Is there something similar in SAS (Proc sort or Proc SQL)?

[chang_y_chung_hotmail_com]

...
> In Oracle-SQL there is a "order by nulls last", which
> does exactly the job i'm serching for. Is there
> something similar in SAS (Proc sort or Proc SQL)?
...
No, there isn't.

[pichro]

Thank You. At least i can stop searching for it.