DATA Step, Macro, Functions and more

Moving observations in the required position

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Moving observations in the required position

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;


Accepted Solutions
Solution
‎06-27-2017 10:12 AM
Super User
Posts: 7,782

Re: Moving observations in the required position

Posted in reply to Data_User

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎06-27-2017 10:12 AM
Super User
Posts: 7,782

Re: Moving observations in the required position

Posted in reply to Data_User

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 43

Re: Moving observations in the required position

Posted in reply to KurtBremser

Thanks for your help. Much appreciate it.

Super Contributor
Posts: 275

Re: Moving observations in the required position

Posted in reply to Data_User

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;

 

Super User
Super User
Posts: 7,955

Re: Moving observations in the required position

Posted in reply to Data_User

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 @KurtBremser 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.

Super User
Posts: 5,504

Re: Moving observations in the required position

Posted in reply to Data_User

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;

Contributor
Posts: 43

Re: Moving observations in the required position

Posted in reply to Astounding

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

Super User
Posts: 5,504

Re: Moving observations in the required position

Posted in reply to Data_User

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 "%".

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 124 views
  • 6 likes
  • 5 in conversation