Help using Base SAS procedures

Sort ascending with missing values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Sort ascending with missing values

Hi,

I'm trying to sort a list with missing values "." and the sort function places all the missing values at the beginning followed by ascending values.  Is there a way to sort and put the "." values at the end of the data?

Thanks,

Bo


Accepted Solutions
Solution
‎10-07-2011 10:45 PM
Super User
Posts: 10,028

Sort ascending with missing values

Another workaround way is set missing value to a very large number, then set it back missing value.

data temp;
a=2;output;
a=4;output;
a=.;output;
run;
proc stdize data=temp missing=999999 out=want reponly;run;
proc sort data=want;by a;run;
data want;set want; if a=999999 then a=.;run;

Ksharp

View solution in original post


All Replies
PROC Star
Posts: 7,474

Sort ascending with missing values

If you don't mind the values being sorted in descending order you could use something like:

data test;

  set sashelp.class;

  if _n_ in (3,5,7) then do;

    call missing(name);

  end;

run;

proc sort data=test;

  by descending name;

run;

Super User
Super User
Posts: 7,050

Sort ascending with missing values

Why?

You could add another variable and include that in the sort.

If your sort variable is ID then you could create a 0/1 variable GROUP using this logic.  Then sort by group id and all of the missing values (group=1) will come after the non-missing values.

data want;

   set have;

   group = missing(id);

run;

proc sort data=want;

  by group id;

run;

PROC Star
Posts: 7,474

Sort ascending with missing values

I think there is also another way, namely creating a sortseq table with proc trantab and then using the sortseq option in proc sort. I don't have any experience with it, but possibly someone else can show us the way?

Solution
‎10-07-2011 10:45 PM
Super User
Posts: 10,028

Sort ascending with missing values

Another workaround way is set missing value to a very large number, then set it back missing value.

data temp;
a=2;output;
a=4;output;
a=.;output;
run;
proc stdize data=temp missing=999999 out=want reponly;run;
proc sort data=want;by a;run;
data want;set want; if a=999999 then a=.;run;

Ksharp

Occasional Contributor
Posts: 8

Sort ascending with missing values

Thanks for all the helpful ideas. I used the work around KSharp suggested.

Thanks!

PROC Star
Posts: 7,474

Sort ascending with missing values

Just so it doesn't get lost, I think Howles suggestion was definitely the easiest to implement.  I.e.,

proc sql noprint;

  create table new as

    select *

      from sashelp.class

        order by missing(name) , name

;

quit;

Super User
Posts: 10,028

Sort ascending with missing values

I agree with Art totally. Howles is better than me.

I am happy that learning something new from him again.

Ksharp

PROC Star
Posts: 7,474

Sort ascending with missing values

Ksharp,  Just to be clear .. I was not picking on you.  You deserved to be credited with a correct answer and Howard is not better than you in all areas.  I was simply concerned that his suggested code might be overlooked when it was, in fact, a gem of a solution.

Super User
Posts: 10,028

Sort ascending with missing values

Art. I understand what you mean.To be honest.

in this case, Howles 's way is better than me.

Ksharp

Valued Guide
Posts: 765

Re: Sort ascending with missing values

Hi ... here are a couple other ideas  ...

* some data with missing values;

data class;

set sashelp.class;

name = ifc(ranuni(999) le .3, ' ', name);

run;

* sort non-missing;

proc sort data=class (where=(^missing(name))) out=new;

by name;

run;

* add missing at end;

proc append base=new data=class(where=(missing(name)));

run;

Or ...

* sort on a new variable within SQL, then drop it;

proc sql;

create table new (drop=_x) as

select *, 1 as _x from class (where=(^missing(name)))

union

select *, 2 as _x from class (where=(missing(name)))

order by _x, name;

quit;

Regular Contributor
Posts: 184

Re: Sort ascending with missing values

I thin you are working too hard :-).

create table new as

select *

  from class

order by missing(name) , name

;

MikeZdeb wrote:

Hi ... here are a couple other ideas  ...

* some data with missing values;

data class;

set sashelp.class;

name = ifc(ranuni(999) le .3, ' ', name);

run;

* sort non-missing;

proc sort data=class (where=(^missing(name))) out=new;

by name;

run;

* add missing at end;

proc append base=new data=class(where=(missing(name)));

run;

Or ...

* sort on a new variable within SQL, then drop it;

proc sql;

create table new (drop=_x) as

select *, 1 as _x from class (where=(^missing(name)))

union

select *, 2 as _x from class (where=(missing(name)))

order by _x, name;

quit;

N/A
Posts: 1

Re: Sort ascending with missing values

I really like Howles' solution, it's elegant and simple, but in talking with a coworker he prefers not to use proc sql in his code. Ksharp's solution is good for him but was problematic as the sorting direction needed to change placing the nulls first. He had to change the temporary value in his code to correct this.

The example was a dataset containing multiple sub-actions to a case_id. He was asked which sub-action is the most impacting for each case_id. To accomplish, he sorted the data on multiple columns with case_id as the first criteria. Then he sorted the data again with proc sort nodupkey by case_id to return the top record for each case_id. If his original sorting criteria is correct, he will return the most impacting sub-action for each case_id. However, when he needed to update the sorting direction of a column it placed weight on the nulls which caused the wrong case_id's to be returned.

Our solution was to expand on Ksharp's idea by adding columns using the missing() function. Then we could still use proc sort and not worry if he had to change the sort direction.

data test;

   ismissingcol1 = missing(col1);

   ismissingcol2 = missing(col2);

run;

proc sort data = test;

  by case_id ismissingcol1 col1 ismissingcol2 descending col2;

run;

proc sort nodupkey data = test out = test_deduped (drop=ismissingcol1 ismissingcol2);

  by case_id;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 7664 views
  • 4 likes
  • 7 in conversation