topic Re: Create an ALL entry for a variable by summing the Counts of the other entries by different varia in SAS Programming

Create an ALL entry for a variable by summing the Counts of the other entries by different variables

Cruiser13 — Mon, 20 Sep 2021 16:16:24 GMT

I am trying to create and ALL entry for a variable by summing the Counts of the other entries by different variables in SAS. Here is an example that might help you better understand what I am asking:

Example dataset:

Pet	sex	color	Count
dog	F	B	2
dog	M	B	5
dog	F	W	4
dog	M	W	1
dog	F	Y	7
dog	M	Y	3
cat	F	B	5
cat	M	B	2
cat	F	W	2
cat	M	W	3
cat	F	Y	6
cat	M	Y	2
bird	F	B	1
bird	M	B	1
bird	F	W	3
bird	M	W	2
bird	F	Y	2
bird	M	Y	4

I want to use SAS to create a Pet called 'ALL' that sums the Count by sex and color.

For this example, it would look like this:

ALL	F	B	8
ALL	M	B	8
ALL	F	W	9
ALL	M	W	6
ALL	F	Y	15
ALL	M	Y	9

How would I use SAS to create the Pet = 'ALL' part?

Thanks in Advance!

Re: Create an ALL entry for a variable by summing the Counts of the other entries by different varia

Reeza — Mon, 20 Sep 2021 16:22:39 GMT

Look into PROC MEANS and specifically the TYPES/WAYS statements. But unless you want the actual word "all" this will get you want you want, and you can add ALL after. If you need it at multiple levels, ie ALL, plus by individual pet, then PROC TABULATE or PROC MEANS can also do that but I may be overcomplicating what you'd want.

proc means data=have;
class sex color;
ways sex*color;
output out=want N=;
run;

proc freq data=have;
table sex*color / list;
run;

Re: Create an ALL entry for a variable by summing the Counts of the other entries by different varia

PaigeMiller — Mon, 20 Sep 2021 16:51:22 GMT

proc summary data=have;
    class pet sex color;
    types sex*color;
    var count;
    output out=_sums_ sum=;
run;

The rows of the output table have missing values for the variable named PET. If you really have to have PET="ALL", a simple data step after PROC SUMMARY will get you there (or alternatively a custom format will do that if that works better for you).

Re: Create an ALL entry for a variable by summing the Counts of the other entries by different varia

Cruiser13 — Mon, 20 Sep 2021 20:30:22 GMT

Thank you!