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;
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
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
Thanks for your help. Much appreciate it.
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;
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.
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;
No, it's not the exact positions. The observation position may change. Thanks @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 "%".
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.
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.