How to create columns using values from one column in SQL (PROC SQL in SAS)

Question

I am using PROC SQL on SAS.

The table on the left is the one I have. I want to create the table on the right, i.e creating as many columns as there are different values for the column 'value' (they are known and only 3 of them : "a", "b" and "c").

The value is the one from "col". If it doesn't exist for corresponding 'value', default value is 0. I tried using GROUP BY and CASE, but I still wound up with a table with 6 lines.

Thanks in advance.

Answer 1

In SAS, you can use proc sql:

proc sql;
    select id,
           sum(case when value = 'a' then col else 0 end) as col_a,
           sum(case when value = 'b' then col else 0 end) as col_b,
           sum(case when value = 'c' then col else 0 end) as col_c
    from t
    group by id;

Answer 2

Consider proc_transpose which is dynamic to any length of values:

** TRANSPOSING DATA SET;
proc sort data=input out=input;
    by id;
run;

proc transpose data=input out=input_transposed(drop=_name_) prefix=col_;
    by id;          
    var col;    
    id value;
run;

To replace missing for zeros run across arrays of numeric columns to call coalesce. Again, this scales to hundreds of potential columns:

data input_transposed;
    set input_transposed;

    array num(*) _numeric_;
    do _n_ = 1 to dim(num);
    num(_n_) = coalesce(num(_n_), 0);
end;