12-02-2019
Lekhnath
Calcite | Level 5
Member since
11-27-2019
- 3 Posts
- 0 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by Lekhnath
Subject Views Posted 1874 12-02-2019 03:01 PM 1875 12-02-2019 03:00 PM 1921 12-02-2019 12:15 PM -
Activity Feed for Lekhnath
- Posted Re: How do I summarizing data with conditional statement and group them by a new column on SAS Programming. 12-02-2019 03:01 PM
- Posted Re: How do I summarizing data with conditional statement and group them by a new column on SAS Programming. 12-02-2019 03:00 PM
- Posted How do I summarizing data with conditional statement and group them by a new column on SAS Programming. 12-02-2019 12:15 PM
12-02-2019
03:01 PM
Hi @ballardw Thank you for your reply. I forgot one very important piece in the question, sorry about that. That is: There is another department "Chuck" which satisfies same criteria of Tim's department plus Rick's department. So, basically the result of Chuck's sales should be sum of Tim's and Rick's. But the data should still be summarized for all Tim, Sam, Henry, Rick and Chuck. Thank you.
... View more
12-02-2019
03:00 PM
Hi @ed_sas_member Thank you for your reply. I forgot one very important piece in the question, sorry about that. That is: There is another department "Chuck" which satisfies same criteria of Tim's department plus Rick's department. So, basically the result of Chuck's sales should be sum of Tim's and Rick's. But the data should still be summarized for all Tim, Sam, Henry, Rick and Chuck. Also, I would like the data as a data table rather than a report. Thank you.
... View more
12-02-2019
12:15 PM
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