topic Re: Creating Subtotals Based on one column in SAS Programming

Creating Subtotals Based on one column

Cheesiepoof05 — Wed, 28 Sep 2022 15:33:22 GMT

I'm new to using SAS Enterprise Guide and am trying to learn how to summarize data and wasn't sure whether code or some SAS EG report is the best method. See below for my current data as well as the results I'm trying to get afterwards. Thank you for your help.

I have the following data:

CATEGORY	NAME	STATUS	COUNT_2021	MARKET_SHARE_2021	COUNT_2022	MARKET_SHARE_2022
ANIMALS	DOG	REAL	5	20.00%	28	1.17%
ANIMALS	DOG	IMAGINARY	17	68.00%	2352	98.66%
ANIMALS	FISH	REAL	.	.	.	.
ANIMALS	BEAR	REAL	3	12.00%	4	0.17%
COLORS	GREEN	IMAGINARY	8	25.00%	7	0.18%
COLORS	ORANGE	REAL	.	.	.	.
COLORS	BLACK	IMAGINARY	24	75.00%	3885	99.82%
SHAPES	CIRCLE	REAL	19	79.17%	27	100.00%
SHAPES	TRIANGLE	IMAGINARY	5	20.83%	.	.
SHAPES	SQUARE	IMAGINARY	.	.	.	.

I'm trying to add subtotal lines that break at Category only and sum the counts and market shares such as: (red lines are what I'm seeking to add)

CATEGORY	NAME	STATUS	COUNT_2021	MARKET_SHARE_2021	COUNT_2022	MARKET_SHARE_2022
ANIMALS	DOG	REAL	5	20.00%	28	1.17%
ANIMALS	DOG	IMAGINARY	17	68.00%	2352	98.66%
ANIMALS	FISH	REAL	.	.	.	.
ANIMALS	BEAR	REAL	3	12.00%	4	0.17%
ANIMALS	TOTAL		25	100.00%	2384	100.00%
COLORS	GREEN	IMAGINARY	8	25.00%	7	0.18%
COLORS	ORANGE	REAL	.	.	.	.
COLORS	BLACK	IMAGINARY	24	75.00%	3885	99.82%
COLORS	TOTAL		32	100.00%	3892	100.00%
SHAPES	CIRCLE	REAL	19	79.17%	27	100.00%
SHAPES	TRIANGLE	IMAGINARY	5	20.83%	.	.
SHAPES	SQUARE	IMAGINARY	.	.	.	.
SHAPES	TOTAL		24	100.00%	27	100.00%

Re: Creating Subtotals Based on one column

PaigeMiller — Wed, 28 Sep 2022 18:15:44 GMT

PROC REPORT makes this relatively easy. You do not need to first create columns with year in the column name; in fact this is a mistake. Use the original (long) data set, rather than the (wide) data set with year in the column name. See Maxim 19.

data fake_data;
input category $ name $ year count marketshare;
cards;
ANIMALS DOG 2021 5 0.002
ANIMALS CAT 2021 9 0.078
ANIMALS BEAR 2021 14 .92
ANIMALS DOG 2022 13 0.042
ANIMALS CAT 2022 22 0.038
ANIMALS BEAR 2022 88 .92
COLORS GREEN 2021 5 0.002
COLORS RED 2021 9 0.078
COLORS STRIPES 2021 14 .92
COLORS GREEN 2022 13 0.042
COLORS RED 2022 22 0.038
COLORS STRIPES 2022 88 .92
;

proc report data=fake_data;
    columns category name year,(count marketshare);
    define year/across 'YEAR';
    define category/group 'CATEGORY';
    define name/group 'GROUP';
    define count/sum 'COUNT';
    define marketshare/sum format=percent10.2 'PERCENT';
    break after category/summarize;
run;

Re: Creating Subtotals Based on one column

Cheesiepoof05 — Wed, 28 Sep 2022 18:17:34 GMT

@PaigeMiller Thanks for the suggestion. Unfortunately my data is in two different locations for the prior quarter and current quarter so I didn't split them apart, rather they were brought in from separate sources. Any way to modify your PROC REPORT to my current format?

Re: Creating Subtotals Based on one column

PaigeMiller — Wed, 28 Sep 2022 18:18:37 GMT

I would modify the data to be in the format I showed. Just because you receive data in a bad format doesn't mean you have to leave it in the bad format.

Re: Creating Subtotals Based on one column

Cheesiepoof05 — Wed, 28 Sep 2022 18:35:06 GMT

@PaigeMiller Even though this report is the last step of my process? I understand if I was building a database, but I'm just creating a quarterly report.

Re: Creating Subtotals Based on one column

PaigeMiller — Wed, 28 Sep 2022 18:58:19 GMT

What I would do is exactly what I explained. To me that is the easiest path, and if you ever get 2023 data, then the code I provided will continue to work without modification, whereas your code will have to be modified to create columns that have 2023 in the variable name. Good code has value in the long run.

It's fine if you don't want to do that, that's up to you.

Re: Creating Subtotals Based on one column

Reeza — Wed, 28 Sep 2022 19:00:55 GMT

This report is easier to create if the raw data is in a different form. If you're developing this process I would strongly agree there's a point in redesigning this to be more dynamic and to do the years dynamically. Otherwise next year you're going to have modify the code to account for a new year. And then each year thereafter.
It's easier to make the code cleaner from the start.

If you do it manually you'll also need to add in another variable to control the sort order to have it sorted to the end. Here's an example of how it can be done. Some of this is hardcoded so you'll need to adapt of course. Now imagine adding a new year and/or different category levels.

You would modify your output statement to get the totals needed. This also depends on if you want a table or report.

proc means data=sashelp.cars noprint;
class make model;
types make make*model;


output out=summary sum(mpg_city) = mpg_city sum(mpg_highway) = mpg_highway;
run;

proc sort data=summary;
by make;
run;



data want;
set summary;
by make _type_;
if _type_ = 2 then do;
 model = 'Total';
 sort_order = 9999;
end;
else if first._type_ and _type_ = 3 then sort_order=1;
else sort_order+1;

drop _freq_;
run;

proc sort data=want;
by make sort_order;
run;

Re: Creating Subtotals Based on one column

PaigeMiller — Wed, 28 Sep 2022 20:41:14 GMT

Starting with the data in your original table (the first one you showed)

data re_arrange;
    set have;
    year=2021;
    count=count_2021;
    market_share=market_share_2021;
    output;
    year=2022;
    count=count_2022;
    market_share=market_share_2022;
    output;
    drop count_2021 count_2022 market_share_2021 market_share_2022;
run;

Now this data set will run through my PROC REPORT code without additional modification (if I did it right, I can't test this code because I don't have your data in a usable form). Of course, at this point, you lose the future benefit of not having to change the program when 2023 data is available, the code in this message will have to be modified for 2023. (That again is the benefit that @Reeza and I described, by doing it right the first time, re-writing the entire system of code rather than adding kludge upon kludge)

Re: Creating Subtotals Based on one column

Reeza — Wed, 28 Sep 2022 21:30:36 GMT

data want;
set have;
by category;
output;
if first.category then do;
 count_2021_sum = count_2021;
 count_2022_sum= count_2022;
end;
else do;
 count_2021_sum +count_2021;
count_2022_sum+count_2022;
end;

if last.category then do;
Name='Total';
count_2021 = count_2021_sum;
count_2022=count_2022_sum;
output;
end;

run;

Another manual option - note you need to add in other variables.