SAS Custom Sort

[saslove]

Does the length of the variable have anything to do with this statement? 

My variable length for "c" is $9. And it doesn't work as expected. 

What am I missing? 

proc sql;
create table test as 
select c from mydata
order by mod(input(c,bits24.)+1,10);
quit; /* assuming ASCII encoding */

[FreelanceReinh]

---

@saslove wrote:

(...) it doesn't work as expected. 

order by mod(input(c,bits24.)+1,10);

---

Hi @saslove,

My suggestion was tailored to the eight treatment codes 'A1Y', 'A1Z', ..., 'P' (and their desired sort order) from your initial post. In particular, the parameter values 7 and 27 in

order by mod(input(c,bits24.)+7,27);

were critical. So, it's not surprising that the trick fails if you change these values to 1 and 10, respectively, without a good reason.

---

Does the length of the variable have anything to do with this statement? 

My variable length for "c" is $9.

The maximum length (3) of your original treatment codes went into the length of the BITSw. informat: 8 bits per character, hence w=24.

It doesn't matter if the defined length of the character variable is greater than 3. (In my code example it was 8.) The BITS24. informat reads only the first 24 bits, i.e., the first 3 characters.

If your real treatment codes were longer than 3 characters (e.g. 'Placebo'), the length of the informat would need to be increased. The maximum length is 64 (bits), i.e. 8 characters, though. Also, with a different set of treatment codes the parameter values (7, 27) would change almost certainly. Even worse, it's not guaranteed that suitable values exist at all.

I would not recommend this type of solution for production code, e.g., in a clinical research project. That's why I called it a "fun fact." For practical purposes the solutions provided by novinosrin, mkeintz, ballardw and hashman are clearly preferable because they are much more transparent and stable and easier to adapt (e.g. to new treatment codes).

That said, feel free to post a different set of treatment codes and their desired sort order. Then I can try to come up with another "fun" solution (just for demonstration what's possible with SAS-supplied [in]formats and functions).

[saslove]

Thanks! That makes sense, thanks for all the explanation. It really helps me understand this code. 
For production, I have used @novinosrin's method. 
I did want to try out what @FreelanceReinh suggested, so I was trying on a test window. 

[ChrisNZ]

As @FreelanceReinh said, do not use his [very] clever demonstration in production. 

1. This code is very hard to maintain. Do you understand it? Can you explain it? Did you document it properly?

2. It can give unwanted results if the variable is given new values.