sql group by question

Solved
Occasional Contributor
Posts: 13

sql group by question

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

.......

Accepted Solutions
Solution
‎07-20-2017 08:52 PM
PROC Star
Posts: 8,169

Re: sql group by question

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

All Replies
Super User
Posts: 23,778

Re: sql group by question

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

.......

PROC Star
Posts: 514

Re: sql group by question

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;
``````
Occasional Contributor
Posts: 13

Re: sql group by question

Thank you so much for your help .

Solution
‎07-20-2017 08:52 PM
PROC Star
Posts: 8,169

Re: sql group by question

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

☑ This topic is solved.