Quartz | Level 8

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

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

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;``````
PG
4 REPLIES 4
Opal | Level 21

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

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;``````
PG
Quartz | Level 8

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

Thanks. This code works perfectly.

Super User

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

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;``````
Quartz | Level 8

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

Thank you. The code you shared also works!

Discussion stats
• 4 replies
• 401 views
• 1 like
• 3 in conversation