BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saslove
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

18 REPLIES 18
novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Quartz | Level 8

This worked well. Thanks for the tip! 

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ballardw
Super User

@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
Ammonite | Level 13

@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
Jade | Level 19

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
Ammonite | Level 13

@FreelanceReinh: Very clever ;). 

ChrisNZ
Tourmaline | Level 20
FreelanceReinh
Jade | Level 19

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
Tourmaline | Level 20

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
Jade | Level 19

@novinosrin wrote:

Kolmogorov reincarnated in Germany (...)


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

novinosrin
Tourmaline | Level 20

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
Ammonite | Level 13

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 6631 views
  • 17 likes
  • 7 in conversation