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
Thank you Reeza! Yes, Distinct was needed.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.