Add missing grades to show 0 counts

[chinna0369]

Hello,

I have below data set,

data step1;
	input id 1 trt 2-3 grade 4-5 cat $6-7;
datalines;
1 1 1 A
2 2 1 B
3 1 2 C
4 2 1 A
5 1 1 B
6 2 2 C
7 1 1 A
;
run;

And I am trying to count subjects per grade as below,

proc sort data=step1; by trt cat; run;

proc freq data=step1 noprint;	
	by trt cat;
	table grade / out=step2;
run;

proc sort data=step2; by cat grade; run;
proc transpose data=step2 out=step3;
	by cat grade;
	id trt;
	var count;
run;

With the above code I have below output,

  cat grade _NAME_ _LABEL_ _1 _2123

A	1	COUNT	Frequency Count	2	1
B	1	COUNT	Frequency Count	1	1
C	2	COUNT	Frequency Count	1	1

In my data there are no subjects for Grade 0, 3 , 4, 5. But I want to show those grades for each cat with 0 counts. Is there any procedure here to add 0 to 5 grades under each cat? I am following below procedure, but I am looking for any efficient procedure.

data dummy;
	cat='A';
	grade=0;
	output;
	cat='A';
	grade=1;
	output;
	cat='A';
	grade=2;
	output;
	cat='A';
	grade=3;
	output;
	cat='A';
	grade=4;
	output;
	cat='A';
	grade=5;
	output;
run;

data step4;
	merge step3 dummy;
	by cat grade;
run;

With the above code I have below output,

Obs cat grade _NAME_ _LABEL_ _1 _212345678

A	0			.	.
A	1	COUNT	Frequency Count	2	1
A	2			.	.
A	3			.	.
A	4			.	.
A	5			.	.
B	1	COUNT	Frequency Count	1	1
C	2	COUNT	Frequency Count	1	1

[tarheel13]

https://support.sas.com/resources/papers/proceedings16/10600-2016.pdf

This paper should help you. Also, if you want to make dummy data, it would be more efficient to use do loops.

[andreas_lds · Accepted solution]

Do you need the result as dataset or report?

If a report is acceptable, using proc tabulate or proc report instead of freq+sort+transpose seems to be a good idea, unfortunately not that much can be done to get a single-row-header.

proc format;
   value GradeFmt
      0 = 0
      1 = 1
      2 = 2
      3 = 3
      4 = 4
      5 = 5
   ;
run;

proc report data=step1 completecols completerows;
   columns cat grade trt,(trt=count);
  
   define cat / group;
   define grade / group format=GradeFmt. preloadfmt;
   define trt / across ;
   define count / n;
run;

options missing='0'; 

proc tabulate data=step1 missing;
   format grade gradeFmt.;
   class cat trt;
   class grade / preloadfmt ;
   table cat*grade, trt*n / printmiss;

   keylabel n= ' ';
run;

options missing='.';

[Ksharp]

You want a table or a report ?

data step1;
	input id 1 trt 2-3 grade 4-5 cat $6-7;
datalines;
1 1 1 A
2 2 1 B
3 1 2 C
4 2 1 A
5 1 1 B
6 2 2 C
7 1 1 A
;
run;
data grade;
   do grade=0 to 5;
    output;
   end;
run;
proc sql;
create table temp as select * from
 (select grade from grade),(select distinct cat from step1),(select distinct trt from step1);
quit;
data step11;
 set step1(in=ina) temp;
 w=ina;
run;

proc sort data=step11; by trt cat; run;

proc freq data=step11 noprint;	
	by trt cat;
	table grade / out=step2;
	weight w/zeros;
run;



proc sort data=step2; by cat grade; run;
proc transpose data=step2 out=step3;
	by cat grade;
	id trt;
	var count;
run;