Calcite | Level 5

## Total by multiple rows and additional constraints

Using the dataset below, what I would like is to create total counts by each group_1 and group_2 for frequency and treatment=1 by group_1 group 2

 Group_1 Group_2 treatment frequency 1 202101 0 3 1 202101 1 2 1 202102 1 1 202102 0 1 2 202102 3 2 202102 0 1 3 202101 0 4 3 202101 1 3 3 202102 6 3 202102 0 5 3 202102 1 1 4 202101 0 2 4 202101 1 1 4 202102 0 1

Want:

 Group Group_2 total_frequency treatment 1 202101 5 2 1 202102 2 2 202101 3 2 202102 1 3 202101 7 1 3 202102 12 1 4 202101 3 1 4 202102 1

I tried the following

proc sql;

select distinct group_1, group_2, sum(frequency) as total_frequency, sum(treatment) as treatment //not sure how to add treatment= 1

from have;

group by group_1, group_2;

quit;

quit;

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Total by multiple rows and additional constraints

What is the rule for treatment=1?
COUNT(TREATMENT=1) may be what you need but it doesn't seem to match your input/output - ie GROUP=1 and GROUP2= 202101 has only one treatment=1 but you have a value of 2 in the table.

The next row has one 0 and that isn't counted so you're not counting the presence of values or the values that equal 1 given what you've posted here.
2 REPLIES 2
Super User

## Re: Total by multiple rows and additional constraints

What is the rule for treatment=1?
COUNT(TREATMENT=1) may be what you need but it doesn't seem to match your input/output - ie GROUP=1 and GROUP2= 202101 has only one treatment=1 but you have a value of 2 in the table.

The next row has one 0 and that isn't counted so you're not counting the presence of values or the values that equal 1 given what you've posted here.
Super User

## Re: Total by multiple rows and additional constraints

FYI - you have several questions posted and none are marked as solved. Please mark your questions as solved once you have a solution that works for you.

Discussion stats
• 2 replies
• 427 views
• 0 likes
• 2 in conversation