Count by month and id

sasuser_sk — Tue, 27 Jul 2021 22:26:50 GMT

Hello Everyone. I have this hypothetical data here. I created Total_Months column in proc sql. I have id1324 with 9 sub IDs. this ID occurs every month (Jan to June) but because of sub id it occurs 54 times in data. Other two IDs in data are showing correct Total_Months because of same Sub_ID. Can someone please help fix my code to represent total number of month my id appears ignoring sub_id. I wish to get field like Desired Column. Thank you.

PROC SQL;
CREATE TABLE WORK.Total_Months AS
SELECT DISTINCT t1.ID,
t1.SUB_ID,
t1.Month,
count (Month) as TOTAL_MONTHS
FROM WORK.MONTHS_REV t1
GROUP BY t1.ID
ORDER BY t1.ID,
t1.Month;
QUIT;

ID SUB_ID Month TOTAL_MONTHS Desired Column
1324 7001 202101 54 6
1324 7002 202101 54 6
1324 7003 202101 54 6:

1324 7004 202101 54 6
1324 7005 202101 54 6
1324 7006 202101 54 6
1324 7007 202101 54 6
1324 7008 202101 54 6
1324 7009 202101 54 6
1324 7001 202102 54 6
1324 7002 202102 54 6
1324 7003 202102 54 6
1324 7004 202102 54 6
1324 7005 202102 54 6
1324 7006 202102 54 6
1324 7007 202102 54 6
1324 7008 202102 54 6
1324 7009 202102 54 6
1324 7001 202102.9628 54 6
1324 7002 202102.9628 54 6
1324 7003 202102.9628 54 6
1324 7004 202102.9628 54 6
1324 7005 202102.9628 54 6
1324 7006 202102.9628 54 6
1324 7007 202102.9628 54 6
1324 7008 202102.9628 54 6
1324 7009 202103 54 6
1324 7001 202104 54 6
1324 7002 202104 54 6
1324 7003 202104 54 6
1324 7004 202104 54 6
1324 7005 202104 54 6
1324 7006 202104 54 6
1324 7007 202104 54 6
1324 7008 202104 54 6
1324 7009 202104 54 6
1324 7001 202105 54 6
1324 7002 202105 54 6
1324 7003 202105 54 6
1324 7004 202105 54 6
1324 7005 202105 54 6
1324 7006 202105 54 6
1324 7007 202105 54 6
1324 7008 202105 54 6
1324 7009 202105 54 6
1324 7001 202106 54 6
1324 7002 202106 54 6
1324 7003 202106 54 6
1324 7004 202106 54 6
1324 7005 202106 54 6
1324 7006 202106 54 6
1324 7007 202106 54 6
1324 7008 202106 54 6
1324 7009 202106 54 6
12148 7102 202101 6 6
12148 7102 202102 6 6
12148 7102 202103 6 6
12148 7102 202104 6 6
12148 7102 202105 6 6
12148 7102 202106 6 6
1342 6686 202103 4 4
1342 6686 202104 4 4
1342 6686 202105 4 4
1342 6686 202106 4 4

Re: Count by month and id

Reeza — Tue, 27 Jul 2021 22:25:36 GMT

You need to provide your input data....you reference BLG_MO but there's no column like that in your data.
Do you just need a count(distinct BLG_MO)?

Re: Count by month and id

sasuser_sk — Tue, 27 Jul 2021 22:31:21 GMT

Thank you Reeza! Yes, Distinct was needed.

topic Count by month and id in SAS Programming

Count by month and id

Re: Count by month and id

Re: Count by month and id