Hi, I have a data which looks like this: Year Month Office Sales_type Sales 2018 Jan Dallas A 10 2018 Jan Dallas B 13 2018 Jan Dallas C 15 2018 Jan Dallas D 20 2018 Jan NY A 5 2018 Jan NY B 9 2018 Jan NY C 7 2018 Jan NY D 17 2018 Jan DC A 15 2018 Jan DC B 19 2018 Jan DC C 17 2018 Jan DC D 19 2018 Feb Dallas A 11 2018 Feb Dallas B 14 2018 Feb Dallas C 16 2018 Feb Dallas D 21 2018 Feb NY A 6 2018 Feb NY B 10 2018 Feb NY C 8 2018 Feb NY D 18 2018 Feb DC A 16 2018 Feb DC B 20 2018 Feb DC C 18 2018 Feb DC D 20 If the office is Dallas and NY and the sales_type is A and B then it belongs to Tim's department If the office is Dallas and the sales_type is C and D then it belongs to Sam's department If the office is NY and DC and the sales_type is C and D then it belongs to Henry's department If the office is DC and the sales_type is A and B then it belongs to Rick's department Now I want the data to be summarized by year, month and department which would look like this: Year Month Dept Sales 2018 Jan Tim 37 2018 Jan Sam 35 2018 Jan Henry 60 2018 Jan Rick 34 2018 Feb Tim 41 2018 Feb Sam 37 2018 Feb Henry 64 2018 Feb Rick 36 2019 Jan Tim ---- 2019 Jan Sam ---- ----- ------ ---- ----- Here is what I have tried: PROC SQL;
CREATE TABLE WORK.Sales_Summary AS
SELECT DISTINCT Month,
(CASE WHEN Sales_type IN ('A','B') AND Office IN ('Dallas','NY') THEN SUM(Sales) END) as Tim,
(CASE WHEN Sales_type IN ('C','D') AND Office IN ('Dallas') THEN SUM(Sales) END) as Sam,
(CASE WHEN Sales_type IN ('C','D') AND Office IN ('NY','DC') THEN SUM(Sales) END) as Henry,
(CASE WHEN Sales_type IN ('A','B') AND Office IN ('DC') THEN SUM(Sales) END) as Rick,
FROM WORK.Sales_Data t1
GROUP BY Year, Month;
QUIT; After I get this I was hoping I could transpose the columns Tim, Sam, Henry and Rick. But the problem is I couldn't even get to summarize the data in those columns correctly. Could you please suggest me the way to get this data summarized in this manner. I use SAS EG 7.15 for making queries. Thank you in Advance!
... View more
