topic Re: Hardcoding format after PROC SUMMARY in SAS Programming

Hardcoding format after PROC SUMMARY

PavelD — Thu, 17 Feb 2022 10:27:53 GMT

Hello!

imagine a dataset and format like this:

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
other = "T"
;
run;

I want to aggregate over my_category, using a custom format (i know, in this simple example I could simply remove my_category from CLASS, but I need to have the column my_category = T in the output dataset).

So I use proc summary:

proc summary nway data = have;
class my_category;
var value;

format my_category $total_format.;

output out = have_summed sum = ;
run;

this produces:

my_category _TYPE_ _FREQ_ value
T           1      6      15

This is expected, so far so good. However, my_category retains the format $total_format, instead of... hardcoding it.

That means that if you remove the format with the following:

data have2;
set have_summed;
format my_category;
run;

the result is:

my_category _TYPE_ _FREQ_ value
A           1      6      15

i.e. "A" instead of "T", which is simply wrong!

In have_summed, the column my_category appears to have the value "T".

If you export have_summed to a CSV, the formatted value "T" is retained. But if you use for example the CATS/CATX function with my_category, it does not use "T" but "A", i.e. the unformatted value. I find this very confusing. What am I missing? Is there a better way?

Platform SAS 9.4.7.0.

Re: Hardcoding format after PROC SUMMARY

PaigeMiller — Thu, 17 Feb 2022 12:10:50 GMT

It's not clear what you are trying to do in the last step

the result is:
my_category _TYPE_ _FREQ_ value
A           1      6      15
i.e. "A" instead of "T", which is simply wrong!

but this is correct. Formatting doesn't change the underlying value. So when you remove the format, the underlying value is returned. Your earlier run of PROC SUMMARY puts all the value of my_category into one category which appears as "T" and the underlying value (unformatted) is the value of "A" (which is what PROC SUMMARY does when it combines categories, it uses the first value alphabetically or numerically to represent combined categories).

So what are you trying to do here?

Re: Hardcoding format after PROC SUMMARY

Kurt_Bremser — Thu, 17 Feb 2022 12:27:49 GMT

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.

Re: Hardcoding format after PROC SUMMARY

PavelD — Thu, 17 Feb 2022 12:14:53 GMT

Hello @PaigeMiller, thanks for your answer.

I find it hard to believe that when aggregating over values, proc summary takes the first value as a label. I cannot think of a situation where that can be useful and not misleading... From your comment I understand that is the expected behaviour?

When you look at the last dataset, you see that the total for category "A" is 15. That is wrong.

I would not care as much and simply keep using the formatted value, except that in some procedures or functions SAS takes the formatted values, sometimes the unformatted value.

What I want is really to force the proc summary to use the format only once in the aggregation, not to assign it permanently to the variable while keeping the unformatted values.

Something like the put function in a data step:

new_var = put(other_var, $my_format.);

In this case new_var is not assigned permanently $my_format.

Maybe my whole approach is not SAS-y enough, maybe there is a better way. I just cant think of any simple way...

Re: Hardcoding format after PROC SUMMARY

JosvanderVelden — Thu, 17 Feb 2022 12:15:08 GMT

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;

Re: Hardcoding format after PROC SUMMARY

PaigeMiller — Thu, 17 Feb 2022 12:22:25 GMT

@PavelD wrote:

Hello @PaigeMiller, thanks for your answer.

I find it hard to believe that when aggregating over values, proc summary takes the first value as a label. I cannot think of a situation where that can be useful and not misleading... From your comment I understand that is the expected behaviour?

What should PROC SUMMARY call a combined group? Well, anyway, yes, it picks the first value alphabetically or numerically in the combined group.

When you look at the last dataset, you see that the total for category "A" is 15. That is wrong.

Actually, your idea of assigning formats and removing them is causing SAS to say "A" has 15. SAS does what you tell it to do. So its not SAS that's wrong, it's your method that causes the wrong answer.

What I want is really to force the proc summary to use the format only once in the aggregation, not to assign it permanently to the variable while keeping the unformatted values.

So, when it does this to do the aggregation, what value should it assign to my_category? I'm really not following what you want here.

In addition, let's move away from the actual code for a second and explain the analysis you are trying to do from a higher level, with no discussion of code. You have data, you want a certain result, what is that desired result (without mentioning code)? Show us the desired result.

Re: Hardcoding format after PROC SUMMARY

acordes — Thu, 17 Feb 2022 12:23:00 GMT

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;

Re: Hardcoding format after PROC SUMMARY

PavelD — Thu, 17 Feb 2022 12:24:54 GMT

Thanks Kurt, but in fact the format is maintained in have2 (i.e. the "T" is still not raw):

Re: Hardcoding format after PROC SUMMARY

Kurt_Bremser — Thu, 17 Feb 2022 12:30:33 GMT

SAS retains the raw value because the variable will also retain its type, so if your category was numeric, but formatted to character values, the formatted values would not be possible:

data have;
input my_category value;
datalines;
1 1
1 0
1 3
2 7
2 3
2 1
;

proc format ;
value total_format
other = "T"
;
run;

proc means nway data=have sum;
class my_category;
var value;
format my_category total_format.;
output out=want sum=;
run;

my_category in WANT is numeric, so the format is needed, and the lowest numeric value for the group is kept.

Re: Hardcoding format after PROC SUMMARY

Kurt_Bremser — Thu, 17 Feb 2022 12:31:51 GMT

For reasons already stated, using a PUT with the format instead of removing the format is the way to go.

Re: Hardcoding format after PROC SUMMARY

Kurt_Bremser — Thu, 17 Feb 2022 12:40:39 GMT

I already found that and edited my post accordingly. I let myself be deceived by appearances.

Re: Hardcoding format after PROC SUMMARY

PavelD — Thu, 17 Feb 2022 12:46:02 GMT

In addition, let's move away from the actual code for a second and explain the analysis you are trying to do from a higher level, with no discussion of code. You have data, you want a certain result, what is that desired result (without mentioning code)? Show us the desired result.

Good point, I should have started with this in the first place. I appreciate your patience, @PaigeMiller and @Kurt_Bremser and others.

Maybe my choice of procs will turn out to be wrong completely.

So, I have raw data, let's say 10 columns, of which one is numeric (the VAR in proc summary). Of this raw data I need to create dozens of partially aggregated datasets, using predefined set of custom formats which I apply to 2-4 columns each time. I need to maintain the structure of the raw dataset, i.e. the 9+1 columns, so I put all 9 in the CLASS. My other idea was that I could have did some remerging after proc summary but I did not like that option, I always try to look for the simplest option. So now I have dozens of aggregated datasets, since they all have the same structure, I wanted to SET them together and create a single dataset, but that failed completely because each dimension adopted the zombie format from the first SET dataset, and as I wrote above the formats differed in each aggregation. Ultimately I wanted to export it into CSV, so I simply exported dozens of CSVs instead of one. This worked. What I meant was that in my situation, it would simply be easier to hardcode formats after each aggregation, perhaps with VVALUE as another member suggested, I thought about that, but I was just hoping there is a neater, cleaner option. If you have any idea or general remarks, I am all ears. Thanks!!

Re: Hardcoding format after PROC SUMMARY

PaigeMiller — Thu, 17 Feb 2022 12:50:17 GMT

I'm sure that PROC SUMMARY could work here, but I'm still not clear what you want (for example, I don't understand what you mean by partially aggregated data sets) and again I request that you SHOW ME the desired output.

Re: Hardcoding format after PROC SUMMARY

data_null__ — Thu, 17 Feb 2022 14:26:54 GMT

If you want PROC SUMMARY to convert formatted values to character using value-labels you can add the multilabel-format option MLF to the class statement;

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 other="Total";
   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;

Re: Hardcoding format after PROC SUMMARY

PavelD — Thu, 17 Feb 2022 14:39:02 GMT

Very weird trick, but it indeed works 😄 Thank you

Re: Hardcoding format after PROC SUMMARY

PavelD — Thu, 17 Feb 2022 14:43:10 GMT

Thanks @PaigeMiller, I am sorry I cannot provide more details, nor the desired result, I think it would be a lots of tables and I dont think it would bring much value. Thanks for your help!!

Re: Hardcoding format after PROC SUMMARY

PaigeMiller — Thu, 17 Feb 2022 14:45:16 GMT

@PavelD wrote:
Thanks @PaigeMiller, I am sorry I cannot provide more details, nor the desired result, I think it would be a lots of tables and I dont think it would bring much value. Thanks for your help!!

You could show us the desired output for your example that your provided. But you haven't done that.

Re: Hardcoding format after PROC SUMMARY

data_null__ — Thu, 17 Feb 2022 14:51:06 GMT

@PavelD I'm not able to follow your description of the ultimate goal. My fault not yours. If you would start a new tread using the have/need scenario I expect you to get many excellent answers that may or may not involve PROC SUMMARY.

@PavelD wrote:

In addition, let's move away from the actual code for a second and explain the analysis you are trying to do from a higher level, with no discussion of code. You have data, you want a certain result, what is that desired result (without mentioning code)? Show us the desired result.

Good point, I should have started with this in the first place. I appreciate your patience, @PaigeMiller and @Kurt_Bremser and others.

Maybe my choice of procs will turn out to be wrong completely.

So, I have raw data, let's say 10 columns, of which one is numeric (the VAR in proc summary). Of this raw data I need to create dozens of partially aggregated datasets, using predefined set of custom formats which I apply to 2-4 columns each time. I need to maintain the structure of the raw dataset, i.e. the 9+1 columns, so I put all 9 in the CLASS. My other idea was that I could have did some remerging after proc summary but I did not like that option, I always try to look for the simplest option. So now I have dozens of aggregated datasets, since they all have the same structure, I wanted to SET them together and create a single dataset, but that failed completely because each dimension adopted the zombie format from the first SET dataset, and as I wrote above the formats differed in each aggregation. Ultimately I wanted to export it into CSV, so I simply exported dozens of CSVs instead of one. This worked. What I meant was that in my situation, it would simply be easier to hardcode formats after each aggregation, perhaps with VVALUE as another member suggested, I thought about that, but I was just hoping there is a neater, cleaner option. If you have any idea or general remarks, I am all ears. Thanks!!

Re: Hardcoding format after PROC SUMMARY

Astounding — Thu, 17 Feb 2022 14:52:13 GMT

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.

Re: Hardcoding format after PROC SUMMARY

data_null__ — Thu, 17 Feb 2022 15:06:57 GMT

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