- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This worked well. Thanks for the tip!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 */
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@FreelanceReinh: Very clever ;).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@FreelanceReinh How on earth...? Well done!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Kolmogorov reincarnated in Germany coz he thought his math was underutilized in Russia as communism prevented innovation. Oh well, even the spirits recognize that 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@novinosrin wrote:
Kolmogorov reincarnated in Germany (...)
??? Strangely enough, he was still alive when I was born ... 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@FreelanceReinh: Perhaps even more strange, I've seen and heard him speak.