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;
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;
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;
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;
This worked well. Thanks for the tip!
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.
@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.
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.
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 */
@FreelanceReinh: Very clever ;).
@FreelanceReinh How on earth...? Well done!
Thank you all for the likes. 🙂
@ChrisNZ wrote:
@FreelanceReinh How on earth...?
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.
Kolmogorov reincarnated in Germany coz he thought his math was underutilized in Russia as communism prevented innovation. Oh well, even the spirits recognize that 🙂
@novinosrin wrote:
Kolmogorov reincarnated in Germany (...)
??? Strangely enough, he was still alive when I was born ... 🙂
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!
@FreelanceReinh: Perhaps even more strange, I've seen and heard him speak.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: