topic Re: Total by multiple rows and additional constraints in SAS Programming

Total by multiple rows and additional constraints

pacman94 — Mon, 22 Feb 2021 20:52:26 GMT

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;

Re: Total by multiple rows and additional constraints

Reeza — Mon, 22 Feb 2021 21:24:35 GMT

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.

Re: Total by multiple rows and additional constraints

Reeza — Mon, 22 Feb 2021 21:25:38 GMT

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.