Hi, I have a sample dataset follows: ******************************************** data sample; infile datalines delimiter=','; input Email $ Product_Type Quantity; datalines; E1,1,2 E1,1,2 E1,1,2 E1,1,2 E1,1,2 E1,3,5 E2,1,1 E2,1,1 E2,1,1 E2,1,1 E2,2,3 ; run; ******************************************** I divided users into different groups based of number of orders they made: ******************************************** proc format; value Orders 1 = '1 order' 2 = '2 orders' 3 - high = '3+ orders' . = 'No purchase'; run; ******************************************** Then I used proc sql to first group data by email and product type, then put them in a subquery to group it again based on product_type and formatted range. ******************************************** proc sql; select product_type, put(number_of_orders, Orders.) as number_of_orders, count(*) as number_of_email_accounts from (select email, product_type, count(*) as number_of_orders from sample group by email, product_type) a group by number_of_orders, a.product_type; quit; ******************************************** Both E1 and E2 have more than 3 orders for Product_Type 1. Output of subquery: Email Product_Type number_of_orders E1 1 5 E1 3 1 E2 1 4 E2 2 1 Final Output: ******************************************** Product_Type number_of_orders number_of_email_accounts 1 3+ orders 1 1 3+ orders 1 2 1 order 1 3 1 order 1 ******************************************** It does give the correct group name, but why the group by clause is not working? Can I get a result like Product_Type number_of_orders number_of_email_accounts 1 3+ orders 2 2 1 order 1 3 1 order 1
... View more