DATA Step, Macro, Functions and more

sql group by question

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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 Smiley Happy 

 

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: 7,363

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

 

View solution in original post


All Replies
Super User
Posts: 17,868

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 Smiley Happy 

 

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: 253

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;
New Contributor
Posts: 4

Re: sql group by question

Thank you so much for your help .

Solution
‎07-20-2017 08:52 PM
PROC Star
Posts: 7,363

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 170 views
  • 3 likes
  • 4 in conversation