sums and counts by group/subgroups

michokwu — Fri, 27 Sep 2019 18:20:10 GMT

Hello,

I'm creating a summary table and want to calculate the weighted counts and sums by group and subgroup. I have this summary by subgroups in different files but I want it as 1 summary table, it goes through 2001 to 2017. When I tried to that, I did not get the desired result.

(See excel sheet attached) and my code below

proc tabulate data=analysis1.in out=analysis1.in_w(drop=_type_);
var weight;
class Year race_sex education;
table Year*race_sex*education
		weight*(n sum);
run;

Regards,

Re: sums and counts by group/subgroups

Kurt_Bremser — Fri, 27 Sep 2019 18:28:02 GMT

Please supply some example input data in usable form (data step with datelines).

Re: sums and counts by group/subgroups

michokwu — Fri, 27 Sep 2019 18:45:57 GMT

Please see Sheet2 attached. Thank you

Re: sums and counts by group/subgroups

ballardw — Fri, 27 Sep 2019 19:32:42 GMT

@michokwu wrote:

Hello,

I'm creating a summary table and want to calculate the weighted counts and sums by group and subgroup. I have this summary by subgroups in different files but I want it as 1 summary table, it goes through 2001 to 2017. When I tried to that, I did not get the desired result.

(See excel sheet attached) and my code below
proc tabulate data=analysis1.in out=analysis1.in_w(drop=_type_);
var weight;
class Year race_sex education;
table Year*race_sex*education
		weight*(n sum);
run;
Regards,

You really need to provide an example of the data and what you expect the results for that example data to actually be. We need an actual data set to test code against. Your existing code does not show any attempt to calculate a "weighted count" and I am not sure that you are using the term appropriately.

If you are not getting the desired result you need to show what the actual desired (or expected) result should be. Other wise have to guess, and with out actual data we can't even guess reasonably.

The way you present your "what I want" does not make a lot of sense with the "count" and "sum" in multiple places. I am moderately sure that Proc tabulate will not line up the columns as shown.

Since your have separate tows in our desired output for race_sex and education you do not want to use race_sex*education as the * used that way nests education within race.

Do you want the "person weight total

You can get a bit closer with :

proc tabulate data=analysis1.in out=analysis1.in_w(drop=_type_);
   var weight;
   class Year race_sex education;
   table year  weight*(n sum);
   table Year*race_sex
   		weight*(n sum);
   table Year*education
   		weight*(n sum);
run;

But that will not make the columns line up in Excel the way you show. That would be possible with the Report Writing Interface in a data step with appropriate data structure but not with Tabulate.

topic sums and counts by group/subgroups in SAS Programming

sums and counts by group/subgroups

Re: sums and counts by group/subgroups

Re: sums and counts by group/subgroups

Re: sums and counts by group/subgroups