topic Re: Proc SQL: order by nulls last in SAS Procedures

Proc SQL: order by nulls last

pichro — Tue, 11 Jan 2011 09:13:57 GMT

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

Re: Proc SQL: order by nulls last

deleted_user — Tue, 11 Jan 2011 11:45:48 GMT

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

Re: Proc SQL: order by nulls last

pichro — Tue, 11 Jan 2011 12:03:07 GMT

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.

Re: Proc SQL: order by nulls last

ballardw — Tue, 11 Jan 2011 17:42:08 GMT

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.

Re: Proc SQL: order by nulls last

sivaji — Wed, 12 Jan 2011 16:39:47 GMT

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

Re: Proc SQL: order by nulls last

pichro — Thu, 13 Jan 2011 11:00:02 GMT

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)?

Re: Proc SQL: order by nulls last

chang_y_chung_hotmail_com — Thu, 13 Jan 2011 15:02:26 GMT

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

Re: Proc SQL: order by nulls last

pichro — Thu, 13 Jan 2011 15:49:08 GMT

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