BookmarkSubscribeRSS Feed
wankata5
Calcite | Level 5

Hi,

 

I've got a data set that looks like this (let's assume the grades are only A B C D):

Test Grades

1      A

1      B

1      B

1      A

1      C

1      B

1      A

1      C

1      B

1      A

 

 

I want to count how many grades are there for each type (including D) and the result to look like the following:

Test Grades Number

1      A           4

1     B            4

1     C           2

1     D           0

 

I am using the followin code:

 

proc sql;
create table want as
select test, grades, count(*) as number
from have
group by est, grades
order by est, grades;

quit;

 

And the problem is that this code doesn't add the row that shows there are 0 D.

 

 

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, it will only count data which is present in the dataset, how can the procedure know that D should be there?

Do your calculation, then merge on a list of exepected values (to get working code post test data in the form of a datastep):

data default_vals;
  input grades $;
  test=1;
datalines;
A
B
C
D
;
run;

/* Your proc sql */

data want;
  merge want default_vals;
  by test grades;
  if number=. then number=0;
run;

 

Shmuel
Garnet | Level 18

In your data there are no "D" nor infinit number of other values.

 

As much as I know you cannot do what you want by sql.

You can do it by data step, deffining array of all expected codes to count intiated as 0,

At end of input you can print or output the counters. Some of them may contain zero.

 

data want;
 set have end=eof
       retain cnt c1-c5 0;
       ix = index('ABCD',grade);
       if ix le 5 then cnt(ix) +1;
       if eof then do;
          do ix=1 to dim(cnt);
              grade = substr('ABCD',ix,1);
              count = cnt(ix);
              output;
          end; 
       end;             
       keep grade count;
 run;
Jagadishkatam
Amethyst | Level 16
data have;
input Test Grades$;
var=1;
cards;
1      A
1      B
1      B
1      A
1      C
1      B
1      A
1      C
1      B
1      A
;

proc format fmtlib;
value $grd
'A'='A'
'B'='B'
'C'='C'
'D'='D'
;
run;

proc summary data=have completetypes nway;
class test;
class  grades / preloadfmt;
var var;
format grades $grd.;
output out=want n=number;
run;
Thanks,
Jag
Ksharp
Super User
data have;
input Test Grades$;
var=1;
cards;
1      A
1      B
1      B
1      A
1      C
1      B
1      A
1      C
1      B
1      A
;

data x;
input x $;
cards;
A
B
C
D
;
run;

proc sql;
select a.test,a.x as grades,coalesce(sum(var),0) as count
 from (
 select * from
 (select distinct test from have),
 (select distinct x from x)
 ) as a left join have as b 
  on a.test=b.test and a.x=b.grades 
  group by a.test,a.x;
quit;
Ksharp
Super User
data have;
input Test Grades$;
cards;
1      A
1      B
1      B
1      A
1      C
1      B
1      A
1      C
1      B
1      A
;

data x;
input Grades $;
cards;
A
B
C
D
;
run;
data temp;
 set have(in=ina) x;
 w=ina;
run;
proc freq data=temp noprint;
table test*Grades/list out=want(where=(test is not missing)) ;
weight w/zeros;
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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 2469 views
  • 2 likes
  • 5 in conversation