@PavelD wrote:
Very weird trick, but it indeed works 😄 Thank you
Not so weird maybe when you think about how multilabel formats work. The is no way to use the values when they appear multiple times only labels are unique.
data have;
input my_category $ value @@;
datalines;
A 1 A 0 A 3 B 7 B 3 B 1
;
run;
proc format ;
value $total_format(multilabel)
'A','B'="Total 1"
'A','B'="Total 2"
'A','B'="Total 3"
;
quit;
proc summary nway data = have;
class my_category / mlf;
var value;
format my_category $total_format.;
output out=have_summed sum=;
run;
proc contents varnum;
proc print;
run;
To get the formatted values as raw values in a dataset, reroute the output to a dataset with ODS:
data have;
input my_category $ value;
datalines;
A 1
A 0
A 3
B 7
B 3
B 1
;
proc format ;
value $total_format
other = "T"
;
run;
ods output summary=have2;
proc means nway data=have sum;
class my_category;
var value;
format my_category $total_format.;
run;
ods output close;
Edit: forget this; SAS still uses a raw value and the format for the ODS OUTPUT dataset.
Thanks Kurt, but in fact the format is maintained in have2 (i.e. the "T" is still not raw):
I already found that and edited my post accordingly. I let myself be deceived by appearances.
In stead of
data have2; set have_summed; format my_category; run;
you can try something like
data have2; set have_summed; my_category = put(my_category, $total_format.); run;
you can hardcode any format by using the vvalue function:
data want;
set have;
format temp $total_format.;
temp=value;
new_cat=vvalue(temp);
run;
Perhaps you should remove NWAY from the PROC SUMMARY statement. If you do that, you will still get exactly what you have now, plus one additional record that aggregates over the entire data set. To identify that additional record, look for _TYPE_=0. (As you know by know, all your current records have _TYPE_=1.) On that _TYPE_=0 record, MY_CATEGORY will have a missing value. So you can get all the numbers you want in one data set, with a small amount of relabeling needed.
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 25. 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.