sasaggregateproc-sqlenterprise-guide4gl

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


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:

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

I need the output as SAS dataset


Solution

  • 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;