topic Re: Custom sort order based on a predefined sort order in New SAS User

Custom sort order based on a predefined sort order

sas-inquirer — Fri, 21 May 2021 16:07:56 GMT

Good morning SAS Gurus,

I am having trouble ending up with a custom sort order in SAS 9.4. I have combined two datasets. The first dataset contained a custom sort order that needs to be maintained. The second dataset contained new observations that needed to be added to the first dataset.

After combining the datasets, the table looks like this (where new obs have SORT_ORDER = .):

data have;
input SORT_ORDER KEY_VAR $;
CARDS;
1 1-2-9
2 1-2-4
4 1-2-5
5 1-2-8
. 1-1-5
. 1-2-7
. 1-3-1
. 1-3-3
;
run;

I can't sort by SORT_ORER because the new observations will be out of order based on the KEY_VAR. I can't sort by the KEY_VAR because it will disrupt the original sort order. I want to end up with this:

data want;
input SORT_ORDER KEY_VAR $;
CARDS;
. 1-1-5
1 1-2-9
2 1-2-4
4 1-2-5
. 1-2-7
5 1-2-8
. 1-3-1
. 1-3-3
;
run;

If I was doing this manually, I would take each new observation and insert it based on the KEY_VAR numeric order.

Where the logic would be if SORT_ORDER = . then insert based on KEY_VAR.

I hope this makes sense. Any suggestions would be greatly appreciated.

Re: Custom sort order based on a predefined sort order

ballardw — Fri, 21 May 2021 17:16:32 GMT

Missiing values make very poor controllers of order in general. So why is "missing" used for order?

It may help to provide what the output of your example should actually look like.

And are there any other variables in your data that need to be considered? If so, how?.

Re: Custom sort order based on a predefined sort order

FreelanceReinh — Fri, 21 May 2021 18:19:22 GMT

Hello @sas-inquirer,

Many non-standard sort orders can be implemented using a suitable ORDER BY clause in a PROC SQL step. A fundamental (mathematical) prerequisite of any of these sort orders is a definition satisfying the properties of a so called linear order (cf. https://en.wikipedia.org/wiki/Total_order). In particular, for any two different records to be sorted there must be no ambiguity as to which one takes precedence of the other. For example, you suggest

SORT_ORDER KEY_VAR
.          1-2-7
5          1-2-8

because you "insert" the new records "based on KEY_VAR." But at the same time you want

SORT_ORDER KEY_VAR
1          1-2-9
.          1-2-7

which seems to follow a different "logic."

Can you provide a rule that is applicable to any two (SORT_ORDER, KEY_VAR) pairs?

Re: Custom sort order based on a predefined sort order

sas-inquirer — Fri, 21 May 2021 18:34:44 GMT

Hi Ballardw

You're right, missing values are very poor controllers. I've never had to deal with this type of issue before. Once I can figure out the sort order issue, I will redo the sort order variable so that each observation has a value.

The output will be:
NEW_SORT_ORDER KEY_VAR
1 1-1-5
2 1-2-9
3 1-2-4
4 1-2-5
5 1-2-7
6 1-2-8
7 1-3-1
8 1-3-3

No other variables need to be considered for the purpose of sorting. There are many other variables but they are directly associated with the KEY_VAR (i.e. first digit represents something, second something else etc.) The KEY_VAR is unique.

The problem is that the original table represents a report that the user wants ordered in a specific way. My role is to update this report with some new KEY_VAR's but maintain any sort order that existed in the original report. The new KEY_VAR's are to be inserted based on their KEY_VAR numeric order without disrupting the original sort order.

Perhaps some context may help. Let's say the report represents grocery items from a store and each item is assigned a unique KEY which represents the manufacturer, the country and the type of food. The new KEY_VARs are new items that the store is offering. Management is very interested in ORANGES and so is used to seeing the report with ORANGES (1-2-9) followed by BANANAS (1-2-4) followed by APPLES (1-2-8) and so a SORT_ORDER variable was created. Unfortunately, the KEY_VAR cannot be changed. The store now offers APRICOTS and has assigned the key (1-2-7). Now the sort order should be ORANGES, BANANAS, APPLES, APRICOTS so the sort is giving priority to the SORT_ORDER first and then the KEY_VAR if cases where the item is new.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Fri, 21 May 2021 18:42:23 GMT

The rule is SORT_ORDER takes precedence over KEY_VAR.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Fri, 21 May 2021 18:47:27 GMT

Sorry, correction in last paragraph: Now the sort order should be ORANGES, BANANAS, APRICOTS, APPLES.

Re: Custom sort order based on a predefined sort order

ballardw — Fri, 21 May 2021 19:18:46 GMT

Still don't know what the desired output should look like.

You mentioned a "report".

If your report is generated with Proc Report or Tabulate it might be possible that PRELOADFMT can control appearance.

The steps would be making a custom format with the option NOTSORTED and placing the values in the desired order. Then using the PRELOADFMT option for the variable along with other options depending on use to help it work.

An example:

proc format;
value orderexample (notsorted)
23 = '23'
1  = '1'
18 = '18'
5  = '5'
;
run;

data example;
   input inorder value;
datalines;
1  45
5  16
18 11
23 0
;

proc report data=example;
   columns inorder value;
   define inorder /group order=data preloadfmt;
   format inorder orderexample.;
run;

The Order=data is one of the helper options but note that the input data order is not the order the report shows for the variable INORDER. Only a few procedures support this option but may be helpful. In proc report the column would have to be a Group variable.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Fri, 21 May 2021 19:40:23 GMT

I think another approach may be to ignore the whole sort issue and take a step back before I even join the tables together.

Table 1 is the original table:

data have_1;
input SORT_ORDER KEY_VAR $;
CARDS;
1 1-2-7
2 1-2-4
3 1-2-5
4 1-2-8
5 1-2-9
;
run;

Table 2 is a new KEY_VAR that needs to be added into the original table:

data have_2;
input KEY_VAR $;
CARDS;
1-2-3
;
run;

How can I insert the new KEY_VAR (1-2-3) from table have_2 before KEY_VAR 1-2-4 without moving any other observations from Table have_1.

data want_1;
input SORT_ORDER KEY_VAR $;
CARDS;
1 1-2-7
. 1-2-3
2 1-2-4
3 1-2-5
4 1-2-8
5 1-2-9
;
run;

So I insert the new var (1-2-3) based on the logic that 1-2-3 comes before 1-2-4. But I don't want to move the first obs (1-2-7) from its current position so I end up with:

data want_final;
input SORT_ORDER KEY_VAR $;
CARDS;
1 1-2-7
2 1-2-3
3 1-2-4
4 1-2-5
5 1-2-8
6 1-2-9
;
run;

I hope that makes more sense.

Re: Custom sort order based on a predefined sort order

ballardw — Fri, 21 May 2021 22:20:17 GMT

What in the data tells that 1-2-3 should come before 1-2-4 but not before 1-2-7???

And just how does this relate to your "report" at all? If there are other variables involved then perhaps examining how the REPORT is made is the important part.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Sat, 22 May 2021 01:51:50 GMT

@ballardw
In all cases where the key_var is new, it should be inserted in numeric order so 1-2-3 should come before 1-2-4. For existing key_var, they should be in the order specified by the sort_order variable. So the idea is to take the original report and manually insert the new points by examining the values and inserting them in numeric order. I have no control over the report design other than being the person who has to update it. Perhaps this is just something that has to be done on a case by case basis manually. Thanks for your assistance.

Re: Custom sort order based on a predefined sort order

mkeintz — Sat, 22 May 2021 13:25:03 GMT

You don't need a sort order rule if the current master data set is (1) already in the desired order, and (2) all relevant values of the sort variable ("sortvar" below), are found at least once in the master data set.

You already stated that condition 1 is true. So assuming (2) is also true, then:

data want;
  set master;
  by key_var notsorted;
  if _n_=1 then do;
    declare hash h (dataset:'newdata',multidata:'Y');
	  h.definekey('key_var');    /* Was "definesortvar"*/
	  h.definedata(all:'Y');
	  h.definedone();
  end;

  output;

  if last.key_var=1 and h.find()=0 then do until (h.find_next()^=0);
	output;
  end;
run;

Actually (2) doesn't have to be entirely true. What you really need is that the NEWDATA data set contains no key_var value not already in MASTER.

I've corrected some bad editing in my code above. Also this program assumes that dataset NEWDATA has all the same variables as MASTER. Otherwise extra variables from the last within-by-group observation in MASTER will be kept in the new observations added for the NEWDATA observations.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Sat, 22 May 2021 03:15:15 GMT

@mkeintz This looks like an interesting approach and I am eager to try it, but I get an error (DATA STEP Component Object failure. Aborted during the COMPILATION phase.) From everything I read on google it would seem to point to a syntax error but unfortunately I have no experience with hash so am having trouble pin-pointing it. Any suggestions?

Re: Custom sort order based on a predefined sort order

FreelanceReinh — Sat, 22 May 2021 09:55:29 GMT

@FreelanceReinh wrote:

In particular, for any two different records to be sorted there must be no ambiguity as to which one takes precedence of the other.

(...)

Can you provide a rule that is applicable to any two (SORT_ORDER, KEY_VAR) pairs?

@sas-inquirer wrote:
The rule is SORT_ORDER takes precedence over KEY_VAR.

The rule must refer to values, not only variable names.

Example:

Suppose that this is an existing record in the original table:

SORT_ORDER KEY_VAR
42         3-5-7

Now this new record is to be inserted:

SORT_ORDER KEY_VAR
.          3-5-11

A rule for a well-defined sort order based on SORT_ORDER and KEY_VAR must decide whether this new record would appear (somewhere) before or (somewhere) after the above existing record after insertion.

Re: Custom sort order based on a predefined sort order

ballardw — Sat, 22 May 2021 14:43:35 GMT

@sas-inquirer wrote:
@mkeintz This looks like an interesting approach and I am eager to try it, but I get an error (DATA STEP Component Object failure. Aborted during the COMPILATION phase.) From everything I read on google it would seem to point to a syntax error but unfortunately I have no experience with hash so am having trouble pin-pointing it. Any suggestions?

ANY time you get an error you should copy the log with all the procedure text and all notes, warnings as well as the message, open a text box on the forum with the </> icon and paste the text. The text box is important because SAS often includes diagnostic information that depends text behavior and the message windows on this forum will reformat pasted text which makes those diagnostics less useful or easy to follow.

With that said, look in the log or your code for something that includes two . in the syntax like this.that.something.

The error you get most easily generated that way.

Re: Custom sort order based on a predefined sort order

PGStats — Sat, 22 May 2021 17:29:28 GMT

I don't quite understand your rule for inserting the new records. This would seem more logical:

data have;
input SORT_ORDER KEY_VAR $;
CARDS;
1 1-2-9
2 1-2-4
4 1-2-5
5 1-2-8
. 1-1-5
. 1-2-7
. 1-3-1
. 1-3-3
;

proc sort data=have out=have1; by key_var; run;

data have2;
retain last_order;
set have1;
temp_sort_order = sort_order;
if missing(temp_sort_order) then temp_sort_order = last_order;
else last_order = sort_order;
drop last_order;
run;

proc sort data=have2 out=have3; by temp_sort_order key_var; run;

data want;
set have3;
new_sort_order = _n_;
run;

proc print data=want noobs; run;

If not, please explain.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Sat, 22 May 2021 21:36:53 GMT

@FreelanceReinhard
Right, the rule then would be any new record should be inserted in numerical order so in your example, 3-5-11 would be inserted after 3-5-7.

Re: Custom sort order based on a predefined sort order

FreelanceReinh — Sun, 23 May 2021 09:41:55 GMT

@sas-inquirer wrote:
@FreelanceReinhard
Right, the rule then would be any new record should be inserted in numerical order so in your example, 3-5-11 would be inserted after 3-5-7.

Thanks. Unfortunately, the rules established so far violate the transitivity condition and hence do not define a linear order (see the Wikipedia article I linked earlier).

Consider these three (SORT_ORDER, KEY_VAR) pairs:

SORT_ORDER KEY_VAR
1          1-1-3
2          1-1-1
.          1-1-2

We have

(1, 1-1-3) < (2, 1-1-1) because "The rule is SORT_ORDER takes precedence over KEY_VAR."
(2, 1-1-1) < (., 1-1-2) because "The new KEY_VAR's are to be inserted based on their KEY_VAR numeric order"
Yet, (1, 1-1-3) > (., 1-1-2) because, again, "The new KEY_VAR's are to be inserted based on their KEY_VAR numeric order".

Therefore it is impossible to sort these three records in accordance with the rules. Each of their six permutations violates at least one of the two rules.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Tue, 25 May 2021 10:50:45 GMT

@PGStats. This is certainly the closest a solution has come. If the 1-3-1 and 1-3-3 fell in position 7 and 8 under new_sort_order it would be perfect, but I'm realizing this custom sort is not going to be possible. Thank you for your suggestion and time.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Tue, 25 May 2021 10:55:32 GMT

@FreelanceReinhard - you are right. This is not a logical rule. I need to go back to my client and convince them to either create a new order with the new data included else accept a numerical order. Thanks so much for your time.

Re: Custom sort order based on a predefined sort order

sas-inquirer — Tue, 25 May 2021 10:57:49 GMT

@mkeintz thanks for your code. I liked the approach. Unfortunatly I cannot make it work for my needs. I think this is a case where automation is not possible.