sum by year and month by group

[pacman94]

I have the following dataset, what i would like to do see by each group and yearmonth, total number of records, and sum all the Types

patientID	group	yearmonth	Type
1	ABC	202101	1
2	ABC	202102	1
3	GHI	202102	1
4	DEF	202101	1
5	DEF	202101	0
6	ABC	202102	1
7	DEF	202102	0
8	DEF	202102	0
9	GHI	202102	1
10	GHI	202101	0
11	ABC	202101	0
12	GHI	202101	0

Want:

group	yearmonth	total_participants	Type_1
ABC	202101	2	1
ABC	202102	2	2
DEF	202101	2	1
DEF	202102	2	0
GHI	202101	2	0
GHI	202102	2	2

I tried the following:

proc sql;
select distinct group, yearmonth, count(patientID) as total_participants, sum(Type) as Type
from test; quit;

but it wont' give me what i need

[Reeza · Accepted solution]

Add a GROUP BY since you want your results grouped by group and yearmonth.

proc sql;
select distinct group, yearmonth, count(patientID) as total_participants, sum(Type) as Type
from test
group by group, yearmonth;
 quit;

---

@pacman94 wrote:

I have the following dataset, what i would like to do see by each group and yearmonth, total number of records, and sum all the Types

patientID	group	yearmonth	Type
1	ABC	202101	1
2	ABC	202102	1
3	GHI	202102	1
4	DEF	202101	1
5	DEF	202101	0
6	ABC	202102	1
7	DEF	202102	0
8	DEF	202102	0
9	GHI	202102	1
10	GHI	202101	0
11	ABC	202101	0
12	GHI	202101	0

Want:

group	yearmonth	total_participants	Type_1
ABC	202101	2	1
ABC	202102	2	2
DEF	202101	2	1
DEF	202102	2	0
GHI	202101	2	0
GHI	202102	2	2

I tried the following:

proc sql;
select distinct group, yearmonth, count(patientID) as total_participants, sum(Type) as Type
from test; quit;

but it wont' give me what i need

---