How to sum of column values

[jgos]

Hi There,

I have following code for my SAS query

PROC SQL;
CREATE TABLE WORK."DIRECTOR_ME_2019Q1"n AS
SELECT t1.DIRECTOR_NAME,
/* Medium(Oct-2019) */
(SUM(case when t1.YR=2019 and t1.MT=10 and t1.SEGMENT='Medium' then t1.TotalAGA else 0 end)) AS
'Medium(Oct-2019)'n,
/* Medium(Nov-2019) */
(SUM(case when t1.YR=2019 and t1.MT=11 and t1.SEGMENT='Medium' then t1.TotalAGA else 0 end)) AS
'Medium(Nov-2019)'n,
/* Medium(Dec-2019) */
(SUM(case when t1.YR=2019 and t1.MT=12 and t1.SEGMENT='Medium' then t1.TotalAGA else 0 end)) AS
'Medium(Dec-2019)'n,
/* Medium(Q4-2019) */
(SUM(case when t1.Quarter='2019-Q4' and t1.SEGMENT='Medium' then t1.TotalAGA else 0 end)) AS
'Medium(Q4-2019)'n,
/* Enterprise(Oct-2019) */
(SUM(case when t1.YR=2019 and t1.MT=10 and t1.SEGMENT='Enterprise' then t1.TotalAGA else 0 end)) AS
'Enterprise(Oct-2019)'n,
/* Enterprise(Nov-2019) */
(SUM(case when t1.YR=2019 and t1.MT=11 and t1.SEGMENT='Enterprise' then t1.TotalAGA else 0 end)) AS
'Enterprise(Nov-2019)'n,
/* Enterprise(Dec-2019) */
(SUM(case when t1.YR=2019 and t1.MT=12 and t1.SEGMENT='Enterprise' then t1.TotalAGA else 0 end)) AS
'Enterprise(Dec-2019)'n,
/* Enterprise(Q4-2019) */
(SUM(case when t1.Quarter='2019-Q4' and t1.SEGMENT='Enterprise' then t1.TotalAGA else 0 end)) AS
'Enterprise(Q4-2019)'n
FROM WORK.QUERY_FOR_ACTS_MONTHLYTOTALS t1
WHERE t1.Quarter = '2019-Q4'
GROUP BY t1.DIRECTOR_NAME;
QUIT;

Which produces following table out put which is sent out in an email

Director _Name	Medium (Oct 2019)	Medium (Nov 2019)	Med (Dec 2019)
Tom	10	20	30
John	40	50	60
Harry	70	80	90

I want total to appear at bottom of all the column so it sums up values in each column. Since I am a novice can anyone help me how to do it in current code or in query builder?

Thanks

[PaigeMiller]

I would do this in PROC REPORT, it's pretty simple there. But I don't know how to do this in Query Builder.

[jgos]

Hi PaigeMiller,

Can you show me how syntax would look like. Please keep in mind name of month and quarter will change every quarter?

Thanks

[Reeza]

This can get you started, it's untested as there was no sample input data.

proc means data=query..... SUM;
class director_name YR SEGMENT;
var totalaga;
where quarter = '2019-Q4';
ods output summary = middle;
run;

data middle2;
set middle;
date = mdy(yr, 1, mt);
IDvar = catx("_", segment, put(date, monyy7.);
display_name = catt(Segment, "(",  put(date, monname3.), "-", YR, ")");
run;

proc transpose data=middle2 out=want;
by director_name;
id idvar;
idlabel  display_name;
var sum;
run;

---

@jgos wrote:

Hi PaigeMiller,

Can you show me how syntax would look like. Please keep in mind name of month and quarter will change every quarter?

Thanks

---

[jgos]

Hi Reeza,

Sample data looks like this. Is there anywhere query I need to refer to table it is coming from?

YR	MT	Month	Quarter	DIRECTOR_NAME	SEGMENT	TotalAGA
2019	10	October	2019-Q4	John Smith	Medium	6
2019	10	October	2019-Q4	John Smith	Medium	13
2019	10	October	2019-Q4	Joe Doe	Medium	1
2019	11	November	2019-Q4	Steve Smith	Enterprise	3
2019	1	January	2019-Q1	John Smith	Medium	1
2019	5	May	2019-Q2	Tony Smith	Medium	2

[Reeza]

Replace the query.... with your input data set name and try running it.

[jgos]

Hi Reeza,

I did that and replaced it with my input data set name, However for Proc transpose step it gives me an error that variable SUM not found.

Thanks

Jgos

[Reeza]

Check the data set for the actual variable name then.

[jgos]

Hi Reeza,

Output of want is now as following.

1. Can we hide _name_ and _label_ column?

2. Can we group all enterprise i.e enterprise oct, enterprise nov, Medium Oct, medium nov and add a column for quarter total so it adds up all three months for each segment

3. It is still not summing up totals of each column

DIRECTOR_NAME	_NAME_	_LABEL_	Enterprise_OCT2019	Medium_OCT2019	Enterprise_NOV2019	Medium_NOV2019
A	TotalAGA_Sum	Sum	10	50	90	30
B	TotalAGA_Sum	Sum	20	60	100	40
C	TotalAGA_Sum	Sum	30	70	10	50
D	TotalAGA_Sum	Sum	40	80	20	60

[PaigeMiller]

---

@jgos wrote:

Hi PaigeMiller,

Can you show me how syntax would look like. Please keep in mind name of month and quarter will change every quarter?

Thanks

---

The changing name of the month and quarter is not a problem.

However, you need to show me a representative portion of the data from data set WORK.QUERY_FOR_ACTS_MONTHLYTOTALS t1 that will be used here.

[jgos]

Sample data looks like this

YR	MT	Month	Quarter	DIRECTOR_NAME	SEGMENT	TotalAGA
2019	10	October	2019-Q4	John Smith	Medium	6
2019	10	October	2019-Q4	John Smith	Medium	13
2019	10	October	2019-Q4	Joe Doe	Medium	1
2019	11	November	2019-Q4	Steve Smith	Enterprise	3
2019	1	January	2019-Q1	John Smith	Medium	1
2019	5	May	2019-Q2	Tony Smith	Medium	2

[PaigeMiller]

---

@jgos wrote:

Sample data looks like this

YR	MT	Month	Quarter	DIRECTOR_NAME	SEGMENT	TotalAGA
2019	10	October	2019-Q4	John Smith	Medium	6
2019	10	October	2019-Q4	John Smith	Medium	13
2019	10	October	2019-Q4	Joe Doe	Medium	1
2019	11	November	2019-Q4	Steve Smith	Enterprise	3
2019	1	January	2019-Q1	John Smith	Medium	1
2019	5	May	2019-Q2	Tony Smith	Medium	2

---

Sadly this isn't helpful. Your original desired output did not contain a segment named ENTERPRISE, what should we do with that?

Also, your original desired output contained only three months, all in one quarter, but ... here there are four distinct months, spread over three quarters. What should we do with that?

Please provide a desired output table for this new data.

[jgos]

Sorry for that. My bad. Desired output would look like following. From total data for whole year email output will show only values for current quarter and months. Maybe somewhere in query I can filter on quarter if it makes sense.

Director_Name	Medium (Oct-2019)	Medium (Nov-2019)	Medium (Dec-2019)	Medium (Q4-2019)	Enterprise (Oct-2019)	Enterprise (Nov-2019)	Enterprise (Dec-2019)	Enterprise (Q4-2019)	Total
John Smith	1	2	3	6	10	11	12	33	39
Joe Doe	4	5	6	15	13	14	15	42	57
Steve Smith	7	8	9	24	16	17	18	51	75
Tony Smith	10	11	12	33	19	20	21	60	93
Total	22	26	30	78	58	62	66	186	264

[PaigeMiller]

/* UNTESTED CODE */
data have2;
    set have;
    date=mdy(mt,1,yr);
    quarter=date;
    format date monyy7. qtr yyq.;
run;

proc report data=have2;
    columns director_name segment,(date quarter),total_aga;
    define director_name/group 'Director Name';
    define segment/across 'Segment';
    define date/across;
    define quarter/across;
    define total_aga/sum;
    rbreak after/summarize;
run;
    

[jgos]

Hi PaigeMiller,

I replace have2 with my input data and it returned with an error

Variable date is not on file WORK.QUERY_FOR_ACTS_MONTHLYTOTALS

My input data has following columns for date :

YR : e.g 2019

MT : e.g 10

Month : e.g October

Quarter : e.g 2019-q4

Thanks

Jgos