Solved
Contributor
Posts: 25

# 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;

Accepted Solutions
Solution
‎03-10-2018 10:04 AM
Super User
Posts: 9,886

## 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;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Super User
Posts: 9,886

## Re: complicated sort

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎03-10-2018 10:04 AM
Super User
Posts: 9,886

## 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;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 25

## Re: complicated sort

Thank you very much

PROC Star
Posts: 1,570

## 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;
``````
☑ This topic is solved.