This would be so much easier if your data was modelled more normalized. So my first step would be to do create a new variable Brand. After that a simple SQL does the trick (as would proc means, tabulate, report etc):
data have;
input customer_id ord_month $ channel $ brand_1_flag brand_2_flag transaction_flag amount;
drop brand_1_flag brand_2_flag; length brand $8;
if transaction_flag = 1;
if brand_1_flag = 1 and brand_2_flag = 1 then do;
brand = 'Both';
output;
end;
else do;
if brand_1_flag = 1 then do;
brand = 'Brand 1';
output;
end;
if brand_2_flag = 1 then do;
brand = 'Brand 2';
output;
end;
end;
cards;
1 2016-02 Web 1 0 1 100
1 2016-02 Retail 0 1 1 200
2 2016-03 Web 1 1 1 50
2 2016-04 Others 0 1 1 90
2 2016-04 Others 0 1 1 300
;
proc sql;
select ord_month, sum(amount) as total_amount, count(transaction_flag) as total_transactions, brand, channel
from have
group by ord_month, channel, brand;
quit;
The subsetting IF can also be delayed to a WHERE statement in the SQL.
Hope this helps,
-- Jan
... View more
