I have a transaction dataset with customer number, merchant category, transaction amount, online flag, and domestic/overseas flag.
Merchant category has different observations like Dining, Apparel, Games, Grocery, electronics etc.
Now for each of these merchant categories, I want to get the total amount spent, total number of online transactions, and total number of overseas transaction for each customer. Ex: For Dining, there will be 3 variables: Total amount spent on dining, total number of online transactions for dining, and total numbe rof overseas dining transactions.
So, for 5 different merchant categories, there will be 3 variable each, so total 15 variables in the final dataset, at a customer level.
How do I do this rather than having to write 15 'Case When' statements in proc sql?
This is a job for PROC SUMMARY
proc summary data=have nway;
class merchant_category;
var amount num_transactions num_overseas_transactions;
output out=_stats_ sum=/autoname;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.