topic Re: sql group by question in SAS Programming

sql group by question

LeeSeongWoo_ — Thu, 20 Jul 2017 16:03:29 GMT

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 egg

1 soy

2 milk

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

.......

Re: sql group by question

Reeza — Thu, 20 Jul 2017 16:23:03 GMT

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

.......

Re: sql group by question

kiranv_ — Thu, 20 Jul 2017 16:54:39 GMT

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;

Re: sql group by question

art297 — Thu, 20 Jul 2017 16:57:28 GMT

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

Re: sql group by question

LeeSeongWoo_ — Fri, 21 Jul 2017 00:54:05 GMT

Thank you so much for your help .