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

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

xliu1 — Sat, 11 Jul 2020 23:30:38 GMT

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

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

PGStats — Sun, 12 Jul 2020 00:42:59 GMT

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;

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

xliu1 — Sun, 12 Jul 2020 03:14:21 GMT

Thanks. This code works perfectly.

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

Kurt_Bremser — Mon, 13 Jul 2020 07:28:53 GMT

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;

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

xliu1 — Mon, 13 Jul 2020 13:41:19 GMT

Thank you. The code you shared also works!