How to aggregate number of occure each value per column in table in SAS Enterprise Guide?

Question

I have table in SAS Enterprise Guide like below:

COL1 | COL2  | COL3
-----|-------|------
111  | A     | C
111  | B     | C
222  | A     | D
333  | A     | D

And I need to aggregate abve table to know how many each value in columns occured, so as to have something like below:

COL2_A  | COL2_B | COL3_C | COL3_D
--------|--------|--------|--------
3       | 1      | 2      | 2

Because:

• COL_2A = 3, because in COL2 value "A" exists 3 times and so on...

How can I do that in SAS Enterprise Guide or in PROC SQL ?

I need the output as SAS dataset

Answer 1

Try this

data have;
input COL1 COL2 $ COL3 $;
datalines;
111 A C 
111 B C 
222 A D 
333 A D 
;

data long;
   set have;
   array col COL2 COL3;
   do over col;
      c = col;
      n  = cats(vname(col), '_', c);
      output;
   end;
run;

proc summary data = long nway;
   class n;
   output out = freq(drop = _TYPE_);
run;

proc transpose data = freq out = wide_freq(drop = _:);
   id n;
run;