Moving observations in the required position

[Data_User]

Hi,

Is there a way to get the set of observations in a dataset to the positions we want? For example the first 4 observations in the dataset HAVE1 should be moved to the last 4 observations as shown in WANT1 dataset below.

DATA HAVE1;

LENGTH F1 $8.;

INPUT F1 $ ID$ F2 $ F3 F4 F5;

datalines;

%NAs 1 A 73 87 65

%NAs 1 B 7 19 63

%NAs 1 C 24 24 64

%NAs 1 D 75 63 58

0-10 1 A 73 21 16

0-10 1 B 29 94 17

0-10 1 C 48 6 99

0-10 1 D 26 46 9

11-50 1 A 48 21 10

11-50 1 B 49 37 45

11-50 1 C 8 88 48

11-50 1 D 16 48 7

51-100 1 A 57 95 18

51-100 1 B 64 73 63

51-100 1 C 28 84 95

51-100 1 D 55 84 17

;

run;

DATA WANT1;

LENGTH F1 $8.;

INPUT F1 $ ID$ F2 $ F3 F4 F5;

datalines;

0-10 1 A 73 21 16

0-10 1 B 29 94 17

0-10 1 C 48 6 99

0-10 1 D 26 46 9

11-50 1 A 48 21 10

11-50 1 B 49 37 45

11-50 1 C 8 88 48

11-50 1 D 16 48 7

51-100 1 A 57 95 18

51-100 1 B 64 73 63

51-100 1 C 28 84 95

51-100 1 D 55 84 17

%NAs 1 A 73 87 65

%NAs 1 B 7 19 63

%NAs 1 C 24 24 64

%NAs 1 D 75 63 58

;

run;

[Kurt_Bremser · Accepted solution]

First of all, post your code using the "little running man" icon. Saves us deleting the extra lines that one gets when code was posted into the main posting window.

If the criteria is the % character in f1, then this does what you want:

data have;
length f1 $8.;
input f1 $ id $ f2 $ f3 f4 f5;
datalines;
%NAs 1 A 73 87 65
%NAs 1 B 7 19 63
%NAs 1 C 24 24 64
%NAs 1 D 75 63 58
0-10 1 A 73 21 16
0-10 1 B 29 94 17
0-10 1 C 48 6 99
0-10 1 D 26 46 9
11-50 1 A 48 21 10
11-50 1 B 49 37 45
11-50 1 C 8 88 48
11-50 1 D 16 48 7
51-100 1 A 57 95 18
51-100 1 B 64 73 63
51-100 1 C 28 84 95
51-100 1 D 55 84 17
;
run;

data int1 int2;
set have;
if substr(f1,1,1) = '%'
then output int2;
else output int1;
run;

data want;
set
  int1
  int2
;
run;

proc print data=want noobs;
run;

Result:

  f1      id    f2    f3    f4    f5

0-10      1     A     73    21    16
0-10      1     B     29    94    17
0-10      1     C     48     6    99
0-10      1     D     26    46     9
11-50     1     A     48    21    10
11-50     1     B     49    37    45
11-50     1     C      8    88    48
11-50     1     D     16    48     7
51-100    1     A     57    95    18
51-100    1     B     64    73    63
51-100    1     C     28    84    95
51-100    1     D     55    84    17
%NAs      1     A     73    87    65
%NAs      1     B      7    19    63
%NAs      1     C     24    24    64
%NAs      1     D     75    63    58

[Data_User]

Thanks for your help. Much appreciate it.

[slchen]

You could produce a dummy without %, then sort with dummy, like this:

data temp;
     set have1;
     dummy=compress(translate(f1,'','%'));
run;

proc sort data=temp out=want(drop=dummy);
by dummy;
run;

Or :

proc sql;

create table want as
select * from have1 order by prxchange('s/%//',-1,f1);
quit;

[RW9]

If your outputting categorical data like this, I find it far simpler to give each section a numerical order, you culd break it up and append it like @Kurt_Bremser has shown, but when the order becomes more complicated and you want to sort later on it just starts becoming harder.  An example:

COL1    COL2 

X            y

X            A

Y            X

I want the above order so:

COL1    COL2    COL1_ORD  COL2_ORD

X            y           1                   1

X            A           1                   2

Y            X           2                   1

May seem like effort, but you can sort the above as much as you like, change the orders etc.

[Astounding]

If you are sure that there are exactly 4 observations to move, the program is just like the solution to your earlier problem (moving one observation):

data want;

set have (firstobs=5) have (obs=4);

run;

[Data_User]

No, it's not the exact positions. The observation position may change. Thanks @Astounding

[Astounding]

No problem.  Just for the record, you can still do this in one step, using mildly similar tools:

data want;

set have (where=(f1 ne : '%')) have (where=(f1 =: '%'));

run;

It's untested, but should work if you are identifying the "bottom" columns as those where F1 begins with "%".