topic Re: How to sum of column values in SAS Programming

How to sum of column values

jgos — Tue, 19 Nov 2019 17:07:56 GMT

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

Re: How to sum of column values

PaigeMiller — Tue, 19 Nov 2019 17:11:23 GMT

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

Re: How to sum of column values

Reeza — Tue, 19 Nov 2019 17:38:38 GMT

I would not do this in SQL at all, why not use PROC MEANS which could do this by default without all of the manual coding here. What happens when new quarters are added? You'll need to update this code for each iteration.

PROC TABULATE will create this very nicely for you without all of this and will scale as the data changes. Ideal if you need a report especially and not a data set. If you need a data set I'd still use PROC TABULATE and store the results and then transpose them so it's still dynamic.

Re: How to sum of column values

jgos — Tue, 19 Nov 2019 19:37:44 GMT

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

Re: How to sum of column values

jgos — Tue, 19 Nov 2019 19:39:51 GMT

Hi Reeza,

As per you proc means will be more suitable based on my requirement? Can you show me what would code look like in my case?

Thanks

Re: How to sum of column values

Reeza — Tue, 19 Nov 2019 19:45:16 GMT

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

Re: How to sum of column values

PaigeMiller — Tue, 19 Nov 2019 19:58:27 GMT

@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.

Re: How to sum of column values

jgos — Tue, 19 Nov 2019 20:17:40 GMT

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

Re: How to sum of column values

jgos — Tue, 19 Nov 2019 20:24:17 GMT

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

Re: How to sum of column values

PaigeMiller — Tue, 19 Nov 2019 21:01:07 GMT

@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.

Re: How to sum of column values

jgos — Tue, 19 Nov 2019 21:19:05 GMT

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

Re: How to sum of column values

PaigeMiller — Tue, 19 Nov 2019 21:37:48 GMT

/* 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;

Re: How to sum of column values

Reeza — Tue, 19 Nov 2019 21:57:45 GMT

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

Re: How to sum of column values

jgos — Wed, 20 Nov 2019 16:30:01 GMT

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

Re: How to sum of column values

jgos — Wed, 20 Nov 2019 16:38:20 GMT

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

Re: How to sum of column values

Reeza — Wed, 20 Nov 2019 17:50:27 GMT

Check the data set for the actual variable name then.

Re: How to sum of column values

PaigeMiller — Wed, 20 Nov 2019 17:54:03 GMT

@jgos wrote:

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

But you have to create data set HAVE2, because in the creation of HAVE2, variable DATE is created. You can't leave out that important step.

Re: How to sum of column values

jgos — Wed, 20 Nov 2019 20:48:38 GMT

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

Re: How to sum of column values

jgos — Thu, 21 Nov 2019 21:23:05 GMT

Thank you PaigeMiller. I modified it a little and it works the way I want it.