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

## complicated sort

data test;
input n \$ x \$ y;
datalines;
1    13    18
2    15    8
3    6    13
4    7    14
5    9    5
6    11    6
7    12    7
8    14    9
9    18    11
10    19    12
11    3    20
12    4    19
13    17    3
14    2    4
;
run;

I would like to sort my collection in the following way in the picture

the resultant dataset

data output;
input n \$ x \$ y;
datalines;
1    13    13
2    6    6
3    7    7
4    9    9
5    11    11
6    12    12
7    14    14
8    18    18
9    19    19
10    3    3
11    4    4
12    15    8
13    17    5
14    2    20
;
run;

Thank you for your help

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: complicated sort

I set up some crude code that gets your intended result, but I made all variables in test numeric:

``````data test;
input n x y;
datalines;
1    13    18
2    15    8
3    6    13
4    7    14
5    9    5
6    11    6
7    12    7
8    14    9
9    18    11
10    19    12
11    3    20
12    4    19
13    17    3
14    2    4
;
run;

data second;
set test (keep=y n rename=(n=n2));
n1 = _n_;
x = y;
run;

proc sort data=test;
by x;
run;

proc sort data=second;
by x;
run;

data
third (drop=n1 n2)
nomatch_t (drop = n1 n2 y)
nomatch_s (drop = n n1 x rename=(n2=n))
;
merge
test (in=a)
second (in=b)
;
by x;
if a and b then output third;
else if not b then output nomatch_t;
else output nomatch_s;
run;

proc sort data=third;
by n;
run;

proc sort data=nomatch_t;
by n;
run;

proc sort data=nomatch_s;
by n;
run;

data fourth;
merge
nomatch_t
nomatch_s (drop=n)
;
run;

data want;
set
third
fourth
;
run;``````
4 REPLIES 4
Super User

## Re: complicated sort

Why is x defined character when it holds only numeric values?

Super User

## Re: complicated sort

I set up some crude code that gets your intended result, but I made all variables in test numeric:

``````data test;
input n x y;
datalines;
1    13    18
2    15    8
3    6    13
4    7    14
5    9    5
6    11    6
7    12    7
8    14    9
9    18    11
10    19    12
11    3    20
12    4    19
13    17    3
14    2    4
;
run;

data second;
set test (keep=y n rename=(n=n2));
n1 = _n_;
x = y;
run;

proc sort data=test;
by x;
run;

proc sort data=second;
by x;
run;

data
third (drop=n1 n2)
nomatch_t (drop = n1 n2 y)
nomatch_s (drop = n n1 x rename=(n2=n))
;
merge
test (in=a)
second (in=b)
;
by x;
if a and b then output third;
else if not b then output nomatch_t;
else output nomatch_s;
run;

proc sort data=third;
by n;
run;

proc sort data=nomatch_t;
by n;
run;

proc sort data=nomatch_s;
by n;
run;

data fourth;
merge
nomatch_t
nomatch_s (drop=n)
;
run;

data want;
set
third
fourth
;
run;``````
Obsidian | Level 7

## Re: complicated sort

Thank you very much

Tourmaline | Level 20

## Re: complicated sort

``````data test;
input n x y;
datalines;
1    13    18
2    15    8
3    6    13
4    7    14
5    9    5
6    11    6
7    12    7
8    14    9
9    18    11
10    19    12
11    3    20
12    4    19
13    17    3
14    2    4
;
run;

data want;
if _N_ = 1 then do;
if 0 then set test;
declare hash h(dataset:'test');
h.defineKey('y');
h.defineData('y');
h.defineDone();
declare hiter iter('h');
end;
set test end=last;
array t(100)_temporary_;
if h.find(key:x)=0 then do;output;h.remove();end;
else do;c+1;t(c)=x;end;
if last then do;
rc = iter.first();
do _n_=1 by 1 while (rc = 0);
x= t(_n_);
output;
rc = iter.next();
end;
end;
drop n rc c;
run;
``````
Discussion stats
• 4 replies
• 1030 views
• 0 likes
• 3 in conversation