Help using Base SAS procedures

Add missing values when counting with proc sql

Reply
New Contributor
Posts: 4

Add missing values when counting with proc sql

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.

 

 

Super User
Super User
Posts: 7,392

Re: Add missing values when counting with proc sql

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;

 

Trusted Advisor
Posts: 1,360

Re: Add missing values when counting with proc sql

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;
Trusted Advisor
Posts: 1,128

Re: Add missing values when counting with proc sql

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
Super User
Posts: 9,662

Re: Add missing values when counting with proc sql

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;
Super User
Posts: 9,662

Re: Add missing values when counting with proc sql

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;
Ask a Question
Discussion stats
  • 5 replies
  • 131 views
  • 2 likes
  • 5 in conversation