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
.......
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
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
.......
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;
Thank you so much for your help .
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.