topic Re: Sort ascending with missing values in SAS Procedures

Sort ascending with missing values

bolore — Fri, 07 Oct 2011 18:33:35 GMT

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,

Sort ascending with missing values

art297 — Fri, 07 Oct 2011 18:40:37 GMT

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;

Sort ascending with missing values

Tom — Fri, 07 Oct 2011 19:16:05 GMT

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;

Sort ascending with missing values

art297 — Fri, 07 Oct 2011 19:20:43 GMT

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?

Sort ascending with missing values

Ksharp — Sat, 08 Oct 2011 02:45:34 GMT

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

Re: Sort ascending with missing values

MikeZdeb — Sat, 08 Oct 2011 23:39:51 GMT

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;

Re: Sort ascending with missing values

Howles — Sun, 09 Oct 2011 02:09:09 GMT

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;

Sort ascending with missing values

bolore — Mon, 10 Oct 2011 10:48:36 GMT

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

Thanks!

Sort ascending with missing values

art297 — Mon, 10 Oct 2011 13:10:03 GMT

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;

Sort ascending with missing values

Ksharp — Tue, 11 Oct 2011 01:39:23 GMT

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

I am happy that learning something new from him again.

Ksharp

Sort ascending with missing values

art297 — Tue, 11 Oct 2011 02:47:46 GMT

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.

Sort ascending with missing values

Ksharp — Tue, 11 Oct 2011 06:26:59 GMT

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

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

Ksharp

Re: Sort ascending with missing values

Matt_88 — Wed, 24 Oct 2012 18:07:50 GMT

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;