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

[daisy6]

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!

[Reeza]

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;

[daisy6]

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

[Astounding · Accepted solution]

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;

[daisy6]

It works !Thanks for that !
Do you still have another way to do that?

[Astounding]

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.

[ballardw]

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

[Ksharp]

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;

[rahuljhaver]

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;