Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

counting observations in by group

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

counting observations in by group

[ Edited ]

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


Accepted Solutions
Solution
‎02-08-2016 10:50 AM
Super User
Posts: 17,842

Re: counting observations in by group

PROC FREQ is a great proc for counting occurences.  

 

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: counting observations in by group

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

Solution
‎02-08-2016 10:50 AM
Super User
Posts: 17,842

Re: counting observations in by group

PROC FREQ is a great proc for counting occurences.  

 

proc freq data=have;
table Name*Rating/nopct;
run;
Regular Contributor
Posts: 215

Re: counting observations in by group

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,

Trusted Advisor
Posts: 1,115

Re: counting observations in by group

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;

 

Super User
Posts: 5,085

Re: counting observations in by group

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 357 views
  • 2 likes
  • 5 in conversation