Split a column by values into new columns SQL

Question

I'm trying to learn SAS and I want to split the different rows of a table into different columns, to group the data.

Namely,

Table Detailed

Num    Date   Type Amount    
A1  6/12/2018 Merc   5    
A2  7/3/2014  Merc   10    
A2  6/5/2014  Merc   6    
A2  6/5/2014  Cong   15
A3  5/6/2020  Cong   30   
A4  7/8/2019  Cong   6     
A3  5/6/2020  Fres   7
A4  7/8/2019  Fres   9

and I want to transform in this table

Table Summary

Num    Date   Merc Cong Fres    
A1  6/12/2018  5   
A2  7/3/2014   10    
A2  6/5/2014   6    15   
A3  5/6/2020        30   7  
A4  7/8/2019        6    9

Developed this query but is not working.

PROC SQL;
   CREATE TABLE WORK.Summary AS 
   SELECT t1.Number, 
          t1.Date, 
          t1.Type, 
          (case when t1.Type='Mercearia' then t1.Type) as Merc,
          (case when t1.Type='Congelado' then t1.Type) as Cong,
          (case when t1.Type='Fresco' then t1.Type) as Fres,
      FROM WORK.Detailed t1
END

Thanks in advance!

SQL will work, but PROC TRANSPOSE is a better solution because it's dynamic and you don't need to know the different types ahead of time or account for them. This can get quite cumbersome when you have more types than 3. — Reeza, Sep 24 '19 at 18:46

score 3 · Accepted Answer · answered Sep 24 '19 at 18:47

3

Try a PROC TRANSPOSE instead. It's dynamic so you don't need to know the number of types ahead of time.

proc sort data=detailed; by number date;

proc transpose data=detailed out=Summary;
by number date;
id type;
var amount;
run;

answered Sep 24 '19 at 18:47

Reeza

20,510
4
21
38

score 2 · Answer 2 · answered Sep 24 '19 at 18:42

You are close. You need aggregation and to fix some of the other syntax:

SELECT d.Number, d.Date, 
       MAX(case when d.Type = 'Mercearia' then d.Amount end) as Merc,
       MAX(case when d.Type = 'Congelado' then d.Amount end) as Cong,
       MAX(case when d.Type = 'Fresco' then d.Amount end) as Fres
FROM WORK.Detailed d
GROUP BY d.Number, d.Date;

Split a column by values into new columns SQL

2 Answers2