Non missing records

[Vasundha]

Hi Everyone. I've come here to get some  assistance from the experts like y'all. I've a table which has 3 fields named Fname RollNum Lname. In the RollNum field I've some missing values as well as non missing values. So, I would like to keep all the non missing values first followed by missing values. Looking forward to getting some support. Thanks. 

Data have;
Input Fname $10. RollNum 5. Lname $10.;
Datalines;
Bobby . Michael
Jonas 12345 Jack
Mike . Tyson 
Caroline 23456 Chera
;

Required output;
Jonas 12345 Jack
Caroline 23456 Chera
Bobby . Michael
Mike . Tyson 
Here the RollNum should be in ascending order.

[andreas_lds]

Please check the date step ....

And try:

proc sort data=have(where= (not missing(RollNum))) out=sorted;
   by RollNum;
run;

data want;
   set sorted have(where= (missing(RollNum)));
run;

[Vasundha]

Thankyou very much. It's working. But can't we do it without using proc sort. Please advise. Actually I've another field named marks prior to this post. I've sorted the data with the descending order of Marks. So if I use your code it might disturb the Marks descending order. That shouldn't be done.

[andreas_lds]

Please post the data you have with all relevant variables.

First i used:

data want;
   set 
      have(where= (not missing(RollNum))
      have(where= (missing(RollNum)));
run;

But sas complained that only one where statement is allowed for each dataset in the set statement. Maybe using a view that just selects the obs with non missing RollNum could replace the first usage of have, making proc sort obsolete.

[Vasundha]

I appreciate your efforts on this.

[Tom]

The avoid the multiple WHERE issue you can use IF instead.

data have;
  row+1;
  input Fname :$10. RollNum Lname :$10.;
Datalines;
Bobby . Michael
Jonas 12345 Jack
Mike . Tyson 
Caroline 23456 Chera
;

data want;
  do while (not eof1);
    set have end=eof1;
    if not missing(rollnum) then output;
  end;
  do while (not eof2);
    set have end=eof2;
    if missing(rollnum) then output;
  end;
  stop;
run;

Result:

[Vasundha]

Can't be thankful enough for this