Calculating Monthly subtotals in EG

[deleted_user]

I just got through figuring out how to do subtotals in general, and now I have come across the problem of needing to compute monthly subtotals. Again I do not believe that there is a SAS function that computes these numbers, but there has to be some way to calculate this. Does anyone know how this could be done?

[DaveShea]

I think that you may need to give us a bit more information about what you are doing and what method you are using to create your sub-totals.

SAS is fantastic at being able to group stuff for sub-totals by various aspects of a date (ie Month, Month/Year, Quarter, Day of Week etc.). Your biggest hurdle is really to make sure that your data has its date value stored as proper SAS dates (ie not just text values that look like dates).

So go on, give us some more information........are you new to SAS and only use SAS/EG or are you someone who is more familiar with writing SAS code but are currently using SAS/EG ?

Some typical data and examples of what you hope to for as results may not go amiss in any reply, it helps people to see what you see.

Cheers,

Down-Under-Dave
Wellington
New Zealand

[Cynthia_sas]

Hi:
It really depends on what your data looks like and what EG task you're using to calculate the subtotals and what outcome you want (data set or report).

Most SAS procedures and/or EG tasks have a way to "group" observations. So, for example, if your data has a MONTH variable or a DATE variable, then you could select "GROUP BY" in your task and specify the MONTH variable.

Another thing to consider is -- do you want monthly subtotals in a SAS data set or do you want a REPORT (like HTML or RTF report) with the subtotals???
Whether there's a function that you could use depends on your data. Generally speaking functions work on a row by row basis. So if your data had these variables:
[pre]
product month day1 day2 day3 day4....
shoes jan 100 150 175 100....
shoes feb 99 100 60 100....
shirts jan 120 130 155 150....
shirts feb 89 120 60 150....
[/pre]

Then you could use the SUM function for every row:
[pre]
montot = sum (of day1-day31);
[/pre]

(even if a month did not have 31 days, missing values for specific days would be ignored with the sum function.)

On the other hand, if your data was structured like this:
[pre]
product mon day amt
shoes jan 1 100
shoes jan 2 150
shoes jan 3 175
shoes jan 4 100
shoes feb 1 99
shoes feb 2 100
shoes feb 3 60
shoes feb 4 100
shirts jan 1 120
shirts jan 2 130
shirts jan 3 155
shirts jan 4 150
shirts feb 1 89
shirts feb 2 120
shirts feb 3 60
shirts feb 4 150
[/pre]

Then the SUM function would not be appropriate -- you'd have to use one of the EG tasks or a SAS procedure to create a file or report with monthly subtotals. You could use the Summary Statistics task or the Summary Tables task to get a report of monthly subtotals.

If you wanted to see the "detail" rows AND the monthly subtotals in a report, then you might consider the List Data task and you would group the report by product and month or just by month. In my fake data, I showed a separate month and day variable, although you might actually have a variable with a whole date value, like a SAS date value and in that case, you would need to apply a format in order to group by month or month and year.

At any rate, it is probable that the same method you figured out to do subtotals in general can be extended to the task of computing monthly subtotals. If you are using code, then you would look for a CLASS statement or a BY statement in the syntax. If you are using an EG task, then you would look for a GROUP BY choice in your task. If you are using the Summary Tables task (which creates PROC TABULATE code), then you would drag and drop the ALL "statistic" into the appropriate place in the table when you build it with drag and drop methods.

For more help with this task, you might open a track with Tech Support so someone could walk you through the click-path you would need to follow in your task of choice to get subtotals by month.

cynthia

[deleted_user]

Thanks for the help. I created custom columns of month and day and then just added them both as the classification variables in my columns. Then made sure subtotals were added and it worked. It was actually really easy.

On this same note, does anyone know how to do monthly running totals? Assume that i have a date column and a profit column. What i would want this custom column to do is continually add the previous days profit to the running total until it reaches the end of the month, at which point it would start over. Suggestions?

[Cynthia_sas]

Hi:
Glad that helped.

If you wanted running totals, your choices are to either use a data step program to calculate the running total and add that as a column in a data set or then you could do a List Data Task that showed the running total or a procedure like PROC REPORT that would let you calculate the running total as a report column (not in the data set).

To calculate running totals in a Data step program, you would need to investigate how programs work, how the RETAIN statement works and how to use the FIRST.byvar automatic variable to reset the running total at the first of every month (or LAST.byvar).

The Data step approach is shown in this Tech Support Note:
http://support.sas.com/kb/19/394.html

To modify the Tech Support code for BY group processing, you would only change the code with a BY statement and an IF statement. This Tech Support note illustrates creating a cumulative column based on BY group processing:
http://support.sas.com/kb/24/744.html

cynthia

[deleted_user]

Ok, I have already used that second link that you have provided to set up my cumulative totals field, so I am familiar with it. However, I am new to SAS programming, and I am not entirely sure how to go about implementing the BY and IF statements to create the MTD running totals.

Here is where I am at so far:


data work.calculated1;
set work.query_for_query9446;
by Month Day;
If FIRST.Month = 1 then 'Cum Total Loans Made MTD'n + 'Loans - Total (sum)'n;
run;

This will separate the new field by month, but they are not calculating running totals, they are only returning the first value in the 'Loans - Total (sum)' column, and then outputting that value for every day of the month. Then it sums that value with the first value of the next month and displays that number for every observation within the following month and so on.

I tried to do this same code with Day as my only byvar, but I receive error messages because it is not sorted (which it wouldn't be because the 'Day' field streams 1 through 31 and then starts over again at 1 on the new month)

Any advice would be greatly appreciated.

Thanks,

Ben Message was edited by: bmartin

[Cynthia_sas]

Hi:
The key is NOT to do the addition on the FIRST.MONTH condition -- all you probably want to do at FIRST.MONTH is reset the cumulative variable to 0.

Consider this data sorted by month product and day. I have added what the values for first.month and last.month will look like on every observation. So you can see that when first.month = 1 SAS is reading the first observation for the month -- at this point you want to set your month accumulator variable to 0. But when SAS is reading ROW 2, 3, 4, etc, you want to keep adding those values for AMT to your accumulator variable. In your logic, you were only adding the number for the first row of your group, which will not get you a running total for every month:
[pre]
product month day amt first.month last.month
shirts 01 1 120 1 0
shirts 01 2 130 0 0
shirts 01 3 155 0 0
shirts 01 4 150 0 0
shoes 01 1 100 0 0
shoes 01 2 150 0 0
shoes 01 3 175 0 0
shoes 01 4 100 0 1
**************************change between months**********
shirts 02 1 89 1 0
shirts 02 2 120 0 0
shirts 02 3 60 0 0
shirts 02 4 150 0 0
shoes 02 1 99 0 0
shoes 02 2 100 0 0
shoes 02 3 60 0 0
shoes 02 4 100 0 1
[/pre]

Assume that we want to accumulate a monthly running total, a "grand" running total and for every month, we also want a running total for shirts and shoes. I need several "accumulator variables" -- MONTOT, GRNDTOT, SHIRTTOT and SHOETOT.

What I want on every observation is to add the AMT on that row to both MONTOT and GRNDTOT -- however, I want to conditionally add AMT to either SHIRTTOT or SHOETOT, depending on the value of PRODUCT.

So this program will do that:
[pre]

data mon_data;
infile datalines;
input product $ month $ day amt;
return;
datalines;
shoes 01 1 100
shoes 01 2 150
shoes 01 3 175
shoes 01 4 100
shoes 02 1 99
shoes 02 2 100
shoes 02 3 60
shoes 02 4 100
shirts 01 1 120
shirts 01 2 130
shirts 01 3 155
shirts 01 4 150
shirts 02 1 89
shirts 02 2 120
shirts 02 3 60
shirts 02 4 150
;
run;

proc sort data=mon_data;
by month product day;
run;

data work.calculated1
sumonly(keep=month montot shirttot shoetot);
set work.mon_data;
by Month;

** I like to use an explicit RETAIN statement;
retain montot shirttot shoetot grndtot 0 ;

** Use first.month condition to set cum variables to 0;
if first.month then do;
montot = 0;
shirttot = 0;
shoetot = 0;
end;

** add the amount for every observation;
montot + amt;
grndtot + amt;

** add the amount for shirt and shoes separately;
if product = 'shirts' then shirttot + amt;
else if product = 'shoes' then shoetot + amt;

** output calculated1 -- same number of obs out as obs in;
output calculated1;

** output monthly totals for last.month condition;
if last.month then output sumonly;

run;

proc print data=work.calculated1;
title 'see different running totals';
run;

proc print data=work.sumonly;
title 'summary file';
run;

[/pre]

If you were going to accumulate on DAY, then you'd use slightly different logic, but the basic idea is
the same -- you use FIRST.byvar to initialize or reset your accumulator variable to 0. Then you just add your numbers on every row.

As you can see with SHIRTTOT and SHOETOT -- at some point, the accumulator variable value just repeats on every row because there are no new values to add to the number. They're not worth much as running totals, but if you want to make a summary data set of ONLY monthly totals without products or days -- like SUMONLY -- then they do serve a purpose. By the time the LAST.MONTH observation is being held in the input buffer you have all the information you need to output MONTH, MONTOT, SHIRTTOT and SHOETOT. The program output is shown below.

cynthia

[pre]
see different running totals

Obs product month day amt montot shirttot shoetot grndtot

1 shirts 01 1 120 120 120 0 120
2 shirts 01 2 130 250 250 0 250
3 shirts 01 3 155 405 405 0 405
4 shirts 01 4 150 555 555 0 555
5 shoes 01 1 100 655 555 100 655
6 shoes 01 2 150 805 555 250 805
7 shoes 01 3 175 980 555 425 980
8 shoes 01 4 100 1080 555 525 1080
9 shirts 02 1 89 89 89 0 1169
10 shirts 02 2 120 209 209 0 1289
11 shirts 02 3 60 269 269 0 1349
12 shirts 02 4 150 419 419 0 1499
13 shoes 02 1 99 518 419 99 1598
14 shoes 02 2 100 618 419 199 1698
15 shoes 02 3 60 678 419 259 1758
16 shoes 02 4 100 778 419 359 1858
***************************************************************************************

summary file

Obs month montot shirttot shoetot

1 01 1080 555 525
2 02 778 419 359

[/pre]

[deleted_user]

Thanks, got it working fine.