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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super 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

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super 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
Data_User
Quartz | Level 8

Thanks for your help. Much appreciate it.

slchen
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Quartz | Level 8

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

Astounding
PROC Star

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 2432 views
  • 6 likes
  • 5 in conversation