How to do a summarize for each level of detail in proc sql

[xliu1]

I need to do a calculation for each level of detail in proc sql: count number of courses for each ID, and sum number of Failed for each ID. Here is a sample of data. Notice that COURSE is not a numeric variable, so I cannot use proc summary or proc means. Does anyone have a better idea? Thanks!

ID	COURSE	Failed
1	NGR-7892	1
1	NGR-7945	0
1	NGR-7974	0
2	LIS-5973	0
2	LIS-5942	1
2	NGR-6207	0
2	TTE-6655	1

[PGStats · Accepted solution]

I don't know what to do with the hypothetical case where a given id failed the same course twice. This counts it as two failures:

proc sql;
select 
	id,
	count(distinct course) as nbCourses,
	sum(failed) as nbFailed
from myData
group by ID;
quit;

[xliu1]

Thanks. This code works perfectly.

[Kurt_Bremser]

You can use proc summary with your data:

data have;
input id $ course $ failed;
datalines;
1 NGR-7892  1
1 NGR-7945  0
1 NGR-7974  0
2 LIS-5973  0
2 LIS-5942  1
2 NGR-6207  0
2 TTE-6655  1
;

proc summary data=have nway;
var failed;
class id;
output
  out=want (drop=_type_ rename=(_freq_=count))
  sum(failed)=
;
run;

[xliu1]

Thank you. The code you shared also works!