Help using Base SAS procedures

Proc SQL: order by nulls last

Reply
New Contributor
Posts: 4

Proc SQL: order by nulls last

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
N/A
Posts: 0

Re: Proc SQL: order by nulls last

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
New Contributor
Posts: 4

Re: Proc SQL: order by nulls last

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.
Super User
Posts: 10,516

Re: Proc SQL: order by nulls last

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.
Occasional Contributor
Posts: 17

Re: Proc SQL: order by nulls last

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
New Contributor
Posts: 4

Re: Proc SQL: order by nulls last

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)?
Regular Contributor
Posts: 241

Re: Proc SQL: order by nulls last

...
> 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.
New Contributor
Posts: 4

Re: Proc SQL: order by nulls last

Thank You. At least i can stop searching for it.
Ask a Question
Discussion stats
  • 7 replies
  • 1261 views
  • 0 likes
  • 5 in conversation