topic Re: using keys to create orders for each key in SAS Programming

using keys to create orders for each key

yymissing — Tue, 28 Aug 2018 15:46:55 GMT

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.

Re: using keys to create orders for each key

Reeza — Tue, 28 Aug 2018 15:52:38 GMT

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.

Re: using keys to create orders for each key

Astounding — Tue, 28 Aug 2018 16:00:08 GMT

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;

Re: using keys to create orders for each key

yymissing — Tue, 28 Aug 2018 16:04:45 GMT

yes, that is what I needed. thanks

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

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

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

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

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

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