How to convert a table to be a contingency table

[Yiting]

I have a table like this:

item1	item2	item3
A	B	A
B	A	C
A	C
A	B	A
B	A	B
	B	A
C	A	B
B	B	A

and I would like to count the occruance of each level for each item and turn it into 

	A	B	C	(missing value)
item1	3	3	1	1
item2	3	4	1	1
item3	4	2	1	1

How can I do that? TIA!

[PeterClemmensen · Accepted solution]

One way.. @Yiting does the below code work for you?

data have;
input (item1 item2 item3)(:$);
infile datalines dlm=',';
datalines;
A,B,A
B,A,C
A,C, 
A,B,A
B,A,B
 ,B,A
C,A,B
B,B,A
;

data temp(keep=var value);
   set have;
   array a item:;
   do i=1 to dim(a);
      value=a[i];
      if value=' ' then value='Missing';
      var=vname(a[i]);
      output;
   end;
run;

proc sql;
   create table temp2 as
   select *, count(value) as count
   from temp
   group by var, value
   order by var;
quit;

proc transpose data=temp2 out=want(drop=_NAME_);
    by var;
    id value;
    var count;
run;

[Yiting]

Thanks! Solved the problem!

[PeterClemmensen]

Anytime, glad to help 🙂