using keys to create orders for each key

[yymissing]

Hi there, I have three keys that I will use them join all data sources. I wanted to create an order for the three keys once they are sorted ascending. Here is what the data looks like:

x1	x2	x3
1	1	1
1	1	201
1	20	1
1	20	202
11	1	1
11	1	203
11	21	1
11	21	2

Here is what I wanted:

x1	x2	x3	o1	o2	o3
1	1	1	1	1	1
1	1	201	1	1	2
1	20	1	1	2	1
1	20	202	1	2	2
11	1	1	2	1	1
11	1	203	2	1	2
11	21	1	2	2	1
11	21	2	2	2	2

wanted to do it efficiently and avoid multiple sorting. Thanks.

[Reeza]

What's the logic for the keys, O1, O2, O3?

---

@yymissing wrote:

Hi there, I have three keys that I will use them join all data sources. I wanted to create an order for the three keys once they are sorted ascending. Here is what the data looks like:

x1	x2	x3
1	1	1
1	1	201
1	20	1
1	20	202
11	1	1
11	1	203
11	21	1
11	21	2

Here is what I wanted:

x1	x2	x3	o1	o2	o3
1	1	1	1	1	1
1	1	201	1	1	2
1	20	1	1	2	1
1	20	202	1	2	2
11	1	1	2	1	1
11	1	203	2	1	2
11	21	1	2	2	1
11	21	2	2	2	2

wanted to do it efficiently and avoid multiple sorting. Thanks.

---

[Astounding · Accepted solution]

I can't test it right now, but I think this is where you are headed:

data want;

set have;

by x1 x2 x3;

if first.x1 then do;

   o1 + 1;

   o2 = 1;

   o3 = 1;

end;

else if first.x2 then do;

   o2 + 1;

   o3 = 1;

end;

else if first.x3 then o3 + 1;

run;

[yymissing]

yes, that is what I needed. thanks