sum by year and month by group

pacman94 — Mon, 22 Feb 2021 19:38:02 GMT

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

Re: sum by year and month by group

Reeza — Mon, 22 Feb 2021 19:49:40 GMT

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

topic sum by year and month by group in SAS Programming

sum by year and month by group

Re: sum by year and month by group