BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bolore
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

12 REPLIES 12
art297
Opal | Level 21

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;

Tom
Super User Tom
Super User

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;

art297
Opal | Level 21

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?

Ksharp
Super User

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

bolore
Calcite | Level 5

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

Thanks!

art297
Opal | Level 21

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;

Ksharp
Super User

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

I am happy that learning something new from him again.

Ksharp

art297
Opal | Level 21

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.

Ksharp
Super User

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

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

Ksharp

MikeZdeb
Rhodochrosite | Level 12

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;

Howles
Quartz | Level 8

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;

Matt_88
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 12 replies
  • 28400 views
  • 6 likes
  • 7 in conversation