DATA Step, Macro, Functions and more

complicated sort

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

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


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

View solution in original post


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

Posted in reply to KurtBremser

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 145 views
  • 0 likes
  • 3 in conversation