topic Re: Moving observations in the required position in SAS Programming

Moving observations in the required position

Data_User — Tue, 27 Jun 2017 10:27:19 GMT

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;

Re: Moving observations in the required position

Kurt_Bremser — Tue, 27 Jun 2017 10:37:44 GMT

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

Re: Moving observations in the required position

slchen — Tue, 27 Jun 2017 12:47:24 GMT

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;

Re: Moving observations in the required position

RW9 — Tue, 27 Jun 2017 12:48:22 GMT

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.

Re: Moving observations in the required position

Astounding — Tue, 27 Jun 2017 13:00:34 GMT

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;

Re: Moving observations in the required position

Data_User — Tue, 27 Jun 2017 14:13:33 GMT

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

Re: Moving observations in the required position

Data_User — Tue, 27 Jun 2017 14:14:05 GMT

Thanks for your help. Much appreciate it.

Re: Moving observations in the required position

Astounding — Tue, 27 Jun 2017 14:27:15 GMT

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