DATA Step, Macro, Functions and more

how to count passing rate if there are two different exams for one examee

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

how to count passing rate if there are two different exams for one examee

Hi SAS guys,

I have problem with counting. Here are my data:

data exam;
input regno $6. exam $4. grade $5.;
datalines;
111 AA Pass
111 BB Pass
222 AA Pass
222 BB Fail
333 AA Fail
333 BB Pass
444 AA Pass
444 BB Pass
555 AA Fail
555 BB Fail
666 AA Fail
666 BB Pass
777 AA Pass
777 BB Fail
888 AA Fail
888 BB Pass
999 AA Pass
999 BB Pass
123 AA Fail
123 BB Pass
;
run;

 

I want to know how many candidates pass both of two exams, how many candidates pass AA exam and how many candidates pass BB exam. Thank a lot!


Accepted Solutions
Solution
‎07-19-2017 03:14 PM
Super User
Posts: 5,498

Re: how to count passing rate if there are two different exams for one examee

This approach should work:

 

data want;

set exam;

by regno;

length results $ 20;

retain results;

if first.regno then results = catx(' ', exam, grade);

else results = catx(' ', results, exam, grade);

if last.regno;

run;

 

proc freq data=want;

tables results;

run;

View solution in original post


All Replies
Super User
Posts: 19,772

Re: how to count passing rate if there are two different exams for one examee

Why not just limit it to the "Pass" then?

Assuming a student cannot 'Pass' the same exam twice this should work.

 

proc freq data=exam;
where grade='Pass';
table exam;
run;
Contributor
Posts: 62

Re: how to count passing rate if there are two different exams for one examee

Thank Reeza for the reply. But the freq table only gives me exam AA or BB passing rate. Not both of AA and BB passing rate

Solution
‎07-19-2017 03:14 PM
Super User
Posts: 5,498

Re: how to count passing rate if there are two different exams for one examee

This approach should work:

 

data want;

set exam;

by regno;

length results $ 20;

retain results;

if first.regno then results = catx(' ', exam, grade);

else results = catx(' ', results, exam, grade);

if last.regno;

run;

 

proc freq data=want;

tables results;

run;

Contributor
Posts: 62

Re: how to count passing rate if there are two different exams for one examee

Posted in reply to Astounding
It works !Thanks for that !
Do you still have another way to do that?
Super User
Posts: 5,498

Re: how to count passing rate if there are two different exams for one examee

Another way?  How about:

 

data want;

merge have (where=(exam='AA')) have (where=(exam='BB') rename=(exam=exam2 grade=grade2));

by regno;

run;

 

proc freq data=want;

tables exam * grade * exam2 * grade2 / missing list;

run;

 

I'm pretty sure the first DATA step works as is, but there is a small chance that the combination of WHERE + RENAME requires a small change.

Super User
Posts: 11,343

Re: how to count passing rate if there are two different exams for one examee

Please note that your code as pasted will not run correctly. The message window sometimes "adjusts" blank spaces. Post code after verifying that it runs correctly into a code box opened using the forum {i} menu icon.

the values I got running your code as is generated:

 

regno exam grade
111 AA Pas s
111 BB Pas s
222 AA Pas s
222 BB Fai l
333 AA Fai l
333 BB Pas s
444 AA Pas s
444 BB Pas s
555 AA Fai l
555 BB Fai l
666 AA Fai l
666 BB Pas s
777 AA Pas s
777 BB Fai l
888 AA Fai l
888 BB Pas s
999 AA Pas s
999 BB Pas s
123 AA Fai l
123 BB Pas s
Super User
Posts: 10,023

Re: how to count passing rate if there are two different exams for one examee

data exam;
input regno $ exam $ c $;
datalines;
111 AA Pass
111 BB Pass
222 AA Pass
222 BB Fail
333 AA Fail
333 BB Pass
444 AA Pass
444 BB Pass
555 AA Fail
555 BB Fail
666 AA Fail
666 BB Pass
777 AA Pass
777 BB Fail
888 AA Fail
888 BB Pass
999 AA Pass
999 BB Pass
123 AA Fail
123 BB Pass
;
run;
title 'the number of Both Pass';
proc sql;
 select count(*) as n
  from (select distinct regno 
from exam
where c='Pass'
group by regno 
having count(distinct exam)=2);
quit;
Regular Learner
Posts: 1

Re: how to count passing rate if there are two different exams for one examee

Another way is by using proc transpose and proc freq shown as below:

 

data exam;
input regno $ exam $ c $;
datalines;
111 AA Pass
111 BB Pass
222 AA Pass
222 BB Fail
333 AA Fail
333 BB Pass
444 AA Pass
444 BB Pass
555 AA Fail
555 BB Fail
666 AA Fail
666 BB Pass
777 AA Pass
777 BB Fail
888 AA Fail
888 BB Pass
999 AA Pass
999 BB Pass
123 AA Fail
123 BB Pass
;
run;


proc sort data=exam; by regno; run;

 

proc transpose data=exam out=temp;
by regno;
id exam;
var c;
run;

 

data temp1;
set temp;
result=catx('-',AA,BB);
run;

 

proc freq;
table result;
run;

 

 
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 149 views
  • 3 likes
  • 6 in conversation