topic Re: How to aggregate two rows (observations) in New SAS User

How to aggregate two rows (observations)

heyyou1 — Fri, 19 Nov 2021 14:13:22 GMT

Hello, I am working with a data set that is a report about users.

The relevant columns are:

Category
Zip Code
Active
Revenue

An example of this data is

category | zip code | active | revenue

- "unknown" | 12345 | true | 900

- "Other" | 12345 | true | 1000

Because of the sources of this data, I want to combine "unknown" and "other" so the report accounts for all the unknown categories. I would like to combine this and aggregate where appropriate so that the result looks like:

category | zip code | active | revenue

- "Unknown\Other" | 12345 | true | 1900

How would I do this and bonus points if I can do this without creating a new table.

Re: How to aggregate two rows (observations)

PaigeMiller — Fri, 19 Nov 2021 14:19:26 GMT

Is this problem a table with only two rows, or is it a bigger table with many rows? If it is a bigger table with many rows, what are the rules for combining ... do we combine everything, or by zip code, or by active, or something else?

And the "aggregate" that you mention is really just a sum of the values under revenue? Or did you want somethign else done with revenue?

Re: How to aggregate two rows (observations)

andreas_lds — Fri, 19 Nov 2021 14:20:48 GMT

Define a format, use proc summary (code is untested):

proc format:
  value $categoryfmt
    "unknown", "Other" = "Unknown/Other"
  ;
run;

proc summary data=have nway;
  class category zip_code active;
  var revenue;
  output out=result(drop= _:) sum=;
run;

Re: How to aggregate two rows (observations)

ballardw — Fri, 19 Nov 2021 15:16:54 GMT

Groups created by a format such as @andreas_lds suggests would be honored by report procedures such as Proc Report or Tabulate, almost all the analysis procedures and most will work for graphing procedures.

The nice thing about using a Format instead of altering data is that a different format for a procedure can create different groupings based on single variables.. No actual change to the data set needed. This can be a significant time savings if working with largish data sets as the time needed to add new variables can get noticeable. Not to mention having to constantly add variables for new uses.