BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
daisy6
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Reeza
Super User

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

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
PROC Star

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
Quartz | Level 8
It works !Thanks for that !
Do you still have another way to do that?
Astounding
PROC Star

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
Super User

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
Super User
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
Calcite | Level 5

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;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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