topic Re: counting observations in by group in SAS Data Science

counting observations in by group

mlogan — Mon, 08 Feb 2016 02:37:09 GMT

Hi All,

I have students who get ratings on their courses. I need to know how many total 'Excellent' and 'Satisfactory' each student (by group in this case) got regardless of their course. Can someone help please. Thank you.

ID Name Course Skill_Name Rating
10 Jack English Writing Excellent
10 Jack English Listening Satisfactory
10 Jack English Learning Good
10 Jack Biology Writing Excellent
10 Jack Biology Listening Satisfactory
10 Jack Biology Learning Satisfactory

13 Jill Math Writing Excellent
13 Jill Math Listening Satisfactory
13 Jill Math Learning Good
13 Jill Biology Writing Excellent
13 Jill Biology Listening Excellent
13 Jill Biology Learning Good

Output table will be:
ID Name Excellent Satisfactory
10 Jack 2 3
13 Jill 3 1

Re: counting observations in by group

stat_sas — Mon, 08 Feb 2016 02:48:39 GMT

proc tabulate data=have;
class ID Name Rating;
where rating ne 'Good';
table id*name,Rating=''*n='';
run;

Re: counting observations in by group

Reeza — Mon, 08 Feb 2016 03:08:13 GMT

PROC FREQ is a great proc for counting occurences.

proc freq data=have;
table Name*Rating/nopct;
run;

Re: counting observations in by group

Astounding — Mon, 08 Feb 2016 13:51:33 GMT

Assuming you want a data set in the format that you have shown ...

Assuming that your data set is already sorted by ID ...

Here is one way to program it:

data want;

set have;

by ID;

if first.ID then do;

excellent=0;

satisfactory=0;

end;

if rating='Excellent' then excellent + 1;

else if rating='Satisfactory' then satisfactory + 1;

if last.ID;

drop rating;

run;

Good luck.

Re: counting observations in by group

mlogan — Mon, 08 Feb 2016 15:54:55 GMT

Thanks Reeza, that's really helpful. Can you tell me how do I bring more than one variable to show up in the table. In this case ID in addition to Name.

Thanks,

Re: counting observations in by group

FreelanceReinh — Mon, 08 Feb 2016 23:16:58 GMT

If I may step in here: Yes, you can add ID as an additional "factor" to the TABLE statement, in which case it's useful to add the LIST option so as to keep all combinations in one table:

table ID*Name*Rating / nopct list;

By using the OUT= option and applying PROC TRANSPOSE to the output dataset, you could even get exactly the output table you had envisaged in your original post:

proc freq data=have;
table ID*Name*Rating / nopct list out=cnt;
run;

proc transpose data=cnt out=want(drop=_:);
where rating in: ('Exc', 'Sat');
by id name;
var count;
id rating;
run;

proc print data=want;
run;