topic Re: SAS Custom Sort in SAS Programming

SAS Custom Sort

saslove — Fri, 18 Oct 2019 16:25:09 GMT

I have the following treatments

A1A

A1Y

A1Z

B1A

B1Y

B1Z

But I need to display on the report in the following order based on dose level.

A1Y

A1Z

A1A

B1Y

B1Z

B1A

If I try to use PROC FORMAT, it only displays the numbers which I don't want. Please help how I can custom sort to get the alphabetical dose levels and not mere numbers.

proc format;
value $cohort
'A1Y'=1
'A1Z'=2
'A1A'=3
'B1Y'=4
'B1Z'=5
'B1A'=6
'ND'=7
'P'=8;
run;

Re: SAS Custom Sort

novinosrin — Fri, 18 Oct 2019 16:32:20 GMT

Hi @saslove You sound like a smart and creative person. Why not use your custom formatted-sort with(as) an informat like below using your original idea.

data have;
input var $;
cards;
A1A
A1Y
A1Z
B1A
B1Y
B1Z
;
proc format;
invalue cohort 
'A1Y'=1
'A1Z'=2
'A1A'=3
'B1Y'=4
'B1Z'=5
'B1A'=6
'ND'=7
'P'=8;
run;

data temp;
set have;
new_var=input(var, cohort.);
run;

proc sort data=temp out=want(drop=new_var);
by new_var;
run;

Re: SAS Custom Sort

novinosrin — Fri, 18 Oct 2019 16:39:59 GMT

SQL is more flexible as you can use expressions to order without having to create a temp table like the Datastep and Proc sort

data have;
input var $;
cards;
A1A
A1Y
A1Z
B1A
B1Y
B1Z
;
proc format;
invalue cohort 
'A1Y'=1
'A1Z'=2
'A1A'=3
'B1Y'=4
'B1Z'=5
'B1A'=6
'ND'=7
'P'=8;
run;

proc sql;
create table want as
select *
from have
order by input(var, cohort.);
quit;

Re: SAS Custom Sort

mkeintz — Fri, 18 Oct 2019 17:33:01 GMT

@saslove

Some PROC's support reporting tables ordered the same as the incoming data. For instance PROC FREQ, PROC TABULATE, and PROC MEANS (and the DEFINE statement in PROC REPORT) honor the "ORDER=DATA" option. So, without resorting (pun intended) to formats, you could create a data set view (as opposed to a data set file) with data from your original dataset processed in the desired order, which then can be submitted to the proc, as in:

data vtemp /view=vtemp;
  set sashelp.class (where=(sex='M'))  sashelp.class (where=(sex='F'));
run;

proc tabulate data=vtemp order=data; 
  class sex;
  var height weight;
  tables sex all='Total',
         (height weight)*(mean N);
run;

Here I've prevented the default behavior of PROC TABULATE to have the row with sex="F" precede the row with sex="M". And remember, the data set view (VTEMP here) is merely a process, it does not write data to disk, so this is a fairly efficient approach (certainly faster than actually sorting the data) The actual data is processed only when the view is named in a subsequent step.

You don't even need to order the entire dataset. You only need to order the first encounter with each value of interest. I.e. in my example, you can just write one dummy record with sex="M" followed by a second dummy record with sex="F" preceding the original dataset of interest, as in:

data vtemp2 /view=vtemp2;
  if _n_=1 then do sex='M','F'; output; end;
  set sashelp.class;
  output;
run;
proc tabulate data=vtemp2 order=data; 
  class sex;
  var height weight;
  tables sex all='Total',
         (height weight)*(mean N);
run;

The downside with the 2nd solution is that I've added 2 observations not in the original data set, which would generate erroneous results if all I wanted was a frequency table of SEX. But PROC TABULATE doesn't care in this case, because I asked it to analyze height and weight (missing only in the added obs) classified by sex.

There are ways to avoid this problem too, but I've gone far enough.

Re: SAS Custom Sort

ballardw — Fri, 18 Oct 2019 17:34:53 GMT

@saslove wrote:

I have the following treatments

A1A

A1Y

A1Z

B1A

B1Y

B1Z

But I need to display on the report in the following order based on dose level.

A1Y

A1Z

A1A

B1Y

B1Z

B1A

What dose level? Are those codes dose levels? Your first statement says these are treatments. So you are apparently missing some detail, i.e. the dose levels. Or be more precise in your terminology.

And how did you use the proc format in an attempt to create things in order? Format has nothing to do with sorting unless displaying in a procedure like Freq, Tabulate or Report and such. So show how you attempt to use the format.

A limited number of procedures will use the Preloadfmt option and when available has some restrictions but this shows a table in the desired order:

proc format library=work;
value $cohort (notsorted)
'A1Y'='A1Y'
'A1Z'='A1Z'
'A1A'='A1A'
'B1Y'='B1Y'
'B1Z'='B1Z'
'B1A'='B1A'
'ND' ='ND' 
'P'  ='P'  
;
run;

data example;
  input value $;
datalines;
A1Z
A1A
B1Y
B1Z
B1A
ND 
P 
A1A
B1Y 
ND 
ND 
ND 
A1Y
A1Y
A1Y
A1Y
;


proc tabulate data=example;
   class value /preloadfmt order=data ;
   format value $cohort.;
   table value,
         n
         /printmiss
   ;
run;

Please note: providing data example in a data step and posting code in a code box opened using the forum's {I} icon to preserve formatting, and code to generate output. Also the option on the format (notsorted) is needed to get the order to display that way with the Preloadfmt option.

Proc Report will also allow the Preloadfmt option in the DEFINE statement.

Most other procedures will not honor the sort order of the format and only use the formatted values.

Re: SAS Custom Sort

saslove — Fri, 18 Oct 2019 18:31:51 GMT

This worked well. Thanks for the tip!

Re: SAS Custom Sort

hashman — Sat, 19 Oct 2019 03:28:34 GMT

@saslove:

You have a clear pattern: Sort by the first 2 bytes of VAR, then sort by the re-sequenced third byte. Hence, there's no need to format each complete key-value - or indeed to use a format at all, as it's tedious, counter-productive, and error-prone.

Rather, simply create a control file where the required sequence of the last byte is stored, then join it with what you HAVE by the last byte of VAR, and then just sort accordingly, keeping only the columns you need to keep:

data have ;                                                                                                                             
  input var $ somedata $ ;                                                                                                              
  cards ;                                                                                                                               
A1A  A33                                                                                                                                
A1Y  A11                                                                                                                                
A1Z  A22                                                                                                                                
B1A  B33                                                                                                                                
B1Y  B11                                                                                                                                
B1Z  B22                                                                                                                                
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data control ;                                                                                                                          
  input lastbyte :$1. seq ;                                                                                                             
  cards ;                                                                                                                               
Y  1                                                                                                                                    
Z  2                                                                                                                                    
A  3                                                                                                                                    
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
proc sql ;                                                                                                                              
  create table want as                                                                                                                  
  select have.* from have, control                                                                                                      
  where  char (var, length (var)) = lastbyte                                                                                            
  order  put (var, $2.), seq                                                                                                            
  ;                                                                                                                                     
quit ;

Kind regards

Paul D.

Re: SAS Custom Sort

FreelanceReinh — Sat, 19 Oct 2019 10:21:32 GMT

Fun fact: A numeric expression providing the desired sort order can be constructed from existing informats and functions:

data have;
input c $;
cards;
A1Y
A1Z
A1A
B1Y
B1Z
B1A
ND
P
;

proc sql;
select c from have
order by mod(input(c,bits24.)+7,27);
quit; /* assuming ASCII encoding */

Re: SAS Custom Sort

hashman — Sat, 19 Oct 2019 21:15:55 GMT

@FreelanceReinh: Very clever ;).

Re: SAS Custom Sort

ChrisNZ — Sun, 20 Oct 2019 22:10:28 GMT

@FreelanceReinh How on earth...? Well done!

Re: SAS Custom Sort

FreelanceReinh — Mon, 21 Oct 2019 10:32:06 GMT

Thank you all for the likes. 🙂

@ChrisNZ wrote:

@FreelanceReinh How on earth...?

The desired sort order is essentially alphabetic, except for the third character. But (Y, Z, A) is a cyclic permutation of the sorted triple (A, Y, Z). ---> Idea: The MOD function (with a suitable divisor) applied to the result of an order-preserving character-to-numeric conversion of the eight treatment codes might yield eight numbers in ascending order. An additive shift of the numeric argument might be necessary to avoid an "overflow" (like d-1 + 1 = 0 modulo d) in the middle of that sequence.
Looked through the list "Informats by Category" and found the BITSw. informat for an easy order-preserving character-to-numeric conversion (here: w=3*8).
Was lucky that a brute-force search for integers d and e such that
```
m[i]=mod(input(c[i],bits24.)+e,d)   (i=1, ..., 8; c[1]='A1Y', c[2]='A1Z', ..., c[8]='P')
```
satisfy m[1]<m[2]< ... <m[8] was successful: d=27 and various possible values for e, the smallest of which was e=7.

Re: SAS Custom Sort

novinosrin — Mon, 21 Oct 2019 11:42:27 GMT

Kolmogorov reincarnated in Germany coz he thought his math was underutilized in Russia as communism prevented innovation. Oh well, even the spirits recognize that 🙂

Re: SAS Custom Sort

FreelanceReinh — Mon, 21 Oct 2019 12:39:31 GMT

@novinosrin wrote:

Kolmogorov reincarnated in Germany (...)

??? Strangely enough, he was still alive when I was born ... 🙂

Re: SAS Custom Sort

novinosrin — Mon, 21 Oct 2019 12:48:54 GMT

Oops embarrassing to realize my knowledge of history is awful.

Okay, changing it to Euler's reincarnation in Germany bcoz he foresaw the not so fair secret banking system, so he wanted to impart his knowledge for the industrial prowess of Germany. Hmm, this could change too for the reason I wouldn't be surprised if one is irritated with Merkel's policies. Let me stop right here before moderator steps in. Cheers!

Re: SAS Custom Sort

hashman — Mon, 21 Oct 2019 16:50:02 GMT

@FreelanceReinh: Perhaps even more strange, I've seen and heard him speak.

Re: SAS Custom Sort

saslove — Mon, 21 Oct 2019 23:19:15 GMT

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 */

Re: SAS Custom Sort

FreelanceReinh — Tue, 22 Oct 2019 09:40:39 GMT

@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).

Re: SAS Custom Sort

saslove — Tue, 22 Oct 2019 20:15:30 GMT

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.

Re: SAS Custom Sort

ChrisNZ — Tue, 22 Oct 2019 20:07:15 GMT

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.