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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.