BookmarkSubscribeRSS Feed
pichro
Calcite | Level 5
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
7 REPLIES 7
deleted_user
Not applicable
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
Calcite | Level 5
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
Super User
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
Fluorite | Level 6
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
Calcite | Level 5
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
Obsidian | Level 7
...
> 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
Calcite | Level 5
Thank You. At least i can stop searching for it.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 6547 views
  • 1 like
  • 5 in conversation