SAS Custom Sort

[saslove]

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;

[novinosrin]

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;

[novinosrin · Accepted solution]

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;

[saslove]

This worked well. Thanks for the tip! 

[mkeintz]

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

[ballardw]

---

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

[hashman]

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

[FreelanceReinh]

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

[hashman]

@FreelanceReinh: Very clever ;). 

[ChrisNZ]

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

[FreelanceReinh]

Thank you all for the likes. 🙂

---

@ChrisNZ wrote:

@FreelanceReinh  How on earth...?

---

1. 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.
2. 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).
3. 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.

[novinosrin]

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

[FreelanceReinh]

---

@novinosrin wrote:

Kolmogorov reincarnated in Germany (...)

---

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

[novinosrin]

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!

[hashman]

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