sql group by question

[LeeSeongWoo_]

I am working on a supermarket pos data. Multiple individuals bought various foods. 

Does anyone know how can i create sum each food by group by person?  

 example 🙂 

id    food    

1     milk 

1     milk 

1     egg

1     egg 

1     egg

1     soy 

2     milk 

2     egg 

2     egg 

2     milk 

.......

.......

like this 

id     food_milk      food_egg      food_soy 

1          2                   3                   1

2          2                    2                   0

3          1                    0                    3

4          2                    1                     2

.......

[Reeza]

First summarize the data into the numbers you want (PROC MEANS/SUMMARY) and then transpose the data (PROC TRANSPOSE).

---

@LeeSeongWoo_ wrote:

I am working on a supermarket pos data. Multiple individuals bought various foods. 

Does anyone know how can i create sum each food by group by person?  

 example 🙂 

id    food    

1     milk 

1     milk 

1     egg

1     egg 

1     egg

1     soy 

2     milk 

2     egg 

2     egg 

2     milk 

.......

.......

like this 

id     food_milk      food_egg      food_soy 

1          2                   3                   1

2          2                    2                   0

3          1                    0                    3

4          2                    1                     2

.......

---

[kiranv_]

If you have fewer values then use sql as shown below. else proc transpose is the way to go as suggested by @Reeza

proc sql;
select id, 
       sum(case when food='milk' then 1 else 0 end) as food_milk,
	   sum(case when food='egg' then 1 else 0 end) as food_egg,
	   sum(case when food='soy' then 1 else 0 end) as food_soy
from have
group by id;

[LeeSeongWoo_]

Thank you so much for your help .

[art297 · Accepted solution]

If you don't mind having a total column, the following might be your easiest method:

proc freq data=have;
  table id*food/nocol nocum nopercent norow;
run;

Art, CEO, AnalystFinder.com