Desktop productivity for business analysts and programmers

Summary Table with Daily Total and Month to Date Total

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Summary Table with Daily Total and Month to Date Total

Hi there

I have lots of daily data and I want to create a table with daily total and month to date total like below:

DateDaily Sum TotalMTD Sum Total
Mar 155
Mar 249
Mar 3716
......
(today's date)3xxx
Totalxxxxxx

How can I achieve this in summary tables? BTW, my challenge was to create the last column MTD total.

Thanks in advance

Thomas.K


Accepted Solutions
Solution
‎03-15-2014 04:21 PM
Super User
Posts: 19,080

Re: Summary Table with Daily Total and Month to Date Total

You can do this in EG via point and click but you need 2 steps.

1. Create running totals

2. Create table with overall totals merged in.

To create running totals in EG in query builder

1. Click Add Table, add the original table (self join)

2. Click on Join and ensure the join is on date.

3. Right click the join, change the join condition so that the t1.date>=t2.date

4. In the main query window, add t1.date, t1.variable

5. Add calculated column that is the sum of t2.variable

6. Add a filter on the query so that t1.date<today() (todays date).

View solution in original post


All Replies
PROC Star
Posts: 1,146

Re: Summary Table with Daily Total and Month to Date Total

To be honest, it would be very challenging to do this using either summary tables or the query builder.

This is one of the cases where the data step construct is almost ideal, but there's no way to do that using the Enterprise Guide tasks.

Is it acceptable to use a code node in EG to do this?

Tom

New Contributor
Posts: 4

Re: Summary Table with Daily Total and Month to Date Total

thanks tom for your reply.

I'm actually very new to SAS EG. I used to use SQL to extract data, use script or just manually implement them in Excel before EG was introduced in our company. if this task is even challenging in EG, then what is this better than Excel? Sorry I'm a little bit off topic here. Anyway, back to the topic, I'm very interested  in any method that could achieve this goal in SAS. After all, it could be a better working process in SAS than in EXCEL.

Thanks

Thomas.K

Super User
Super User
Posts: 7,711

Re: Summary Table with Daily Total and Month to Date Total

Well, you can do the below - assuming that your dates are all the same structure:

/* Test data */

data have;

  attrib dte format=$10. anum format=best.;

  dte="Mar 1"; anum=1; output;

  dte="Mar 1"; anum=1; output;

  dte="Mar 1"; anum=1; output;

  dte="Mar 1"; anum=1; output;

  dte="Mar 1"; anum=1; output;

  dte="Mar 2"; anum=1; output;

  dte="Mar 2"; anum=1; output;

  dte="Mar 2"; anum=1; output;

  dte="Mar 2"; anum=1; output;

  dte="Mar 3"; anum=1; output;

  dte="Mar 3"; anum=1; output;

  dte="Mar 3"; anum=1; output;

  dte="Mar 3"; anum=1; output;

  dte="Mar 3"; anum=1; output;

  dte="Mar 3"; anum=1; output;

  dte="Mar 3"; anum=1; output;

;

run;

proc sql;

  create table WANT as

  select  DAILY.DTE,

          DAILY.DAILY_SUM,

          (select SUM(THIS.ANUM)

           from   HAVE THIS

           where  substr(DAILY.DTE,1,3)=substr(THIS.DTE,1,3)

            and   input(strip(substr(THIS.DTE,4)),best.)<=input(strip(substr(DAILY.DTE,4)),best.)

          ) as MTD_TOTAL

  from    (

            select  distinct

                    DTE,

                    SUM(ANUM) as DAILY_SUM

            from    HAVE

            group by DTE

          ) DAILY;

quit;

Respected Advisor
Posts: 4,132

Re: Summary Table with Daily Total and Month to Date Total

The "challenging" bit here is the rolling sums. That's not something easily done with standard SQL. It is though not hard to code for it using a SAS data step.

With SAS you've got both SQL and data step available so there is always a solution. The SQL in below code could be generated using the EG Query Builder. There exists to my knowledge no wizard for the data step required so you need to code this one.

You can't compare Excel with SAS EG. These are two very different tools and both have their area of usage. It's about using the right tool for the right task. I can only advise to embrace SAS EG as it will empower you to do things you were not able to do before.

With SAS you work with data tables and not with spread sheets. The "total" line in your initial post is nothing you would store in a table but you would use a table as source to create a report. There are wizards for this in SAS EG and over time you will also get the skills to code such things for requirements where the wizards are not sufficient (the underlying reporting procedures are much more versatile in ways where it's hard to design a wizard for).

And here the code sample which you can copy/paste into a SAS EG code node and run from there.

data have;
  attrib
    dte informat=date9. format=date9.
    anum informat=16. format=best16.;
  input dte anum;
  datalines;
01Nov2013 1
01Nov2013 1
03Nov2013 1
07Dec2013 1
07Dec2013 1
10Dec2013 1
01Jan2014 1
01Jan2014 1
01Jan2014 1
01Jan2014 1
01Jan2014 1
02Jan2014 1
02Jan2014 1
02Jan2014 1
02Jan2014 1
03Jan2014 1
03Jan2014 1
03Jan2014 1
03Jan2014 1
03Jan2014 1
03Jan2014 1
03Jan2014 1
;
run;

proc sql;
  create view V_daily_sums as
    select dte, sum(anum) as daily_sum_Total
    from have
    group by dte
  ;
quit;

data want;
  set V_daily_sums;
  by dte;
  year=year(dte);
  if year ne lag(year) then call missing(MDT_Sum_Total);
  MDT_Sum_Total+daily_sum_Total;
run;

Solution
‎03-15-2014 04:21 PM
Super User
Posts: 19,080

Re: Summary Table with Daily Total and Month to Date Total

You can do this in EG via point and click but you need 2 steps.

1. Create running totals

2. Create table with overall totals merged in.

To create running totals in EG in query builder

1. Click Add Table, add the original table (self join)

2. Click on Join and ensure the join is on date.

3. Right click the join, change the join condition so that the t1.date>=t2.date

4. In the main query window, add t1.date, t1.variable

5. Add calculated column that is the sum of t2.variable

6. Add a filter on the query so that t1.date<today() (todays date).

Super User
Super User
Posts: 6,843

Re: Summary Table with Daily Total and Month to Date Total

You do not show your actual data, but that just looks like a PROC FREQ output done by month.  Not sure what the bottom row adds to the report over the information that is in the final cumm total.

data have ;

  do day='01MAR2014'd to today() ;

    num = int(10*ranuni(0)+1) ;

    output ;

  end;

  format day yymmdd10. ;

run;

proc freq data=have ;

  table day /nopercent ;

  weight num ;

run;

The FREQ Procedure

                           Cumulative

       day    Frequency     Frequency

-------------------------------------------

2014-03-01           4             4

2014-03-02           2             6

2014-03-03           1             7

2014-03-04           4            11

2014-03-05           6            17

2014-03-06           3            20

2014-03-07           8            28

2014-03-08           9            37

2014-03-09           5            42

2014-03-10           7            49

2014-03-11           5            54

2014-03-12           5            59

2014-03-13           3            62

2014-03-14           1            63

2014-03-15           2            65

New Contributor
Posts: 4

Re: Summary Table with Daily Total and Month to Date Total

Thanks everyone who contributed in this thread. I was too busy to visit here for few days. For the last few days, I was trying to find a SUM OVER RANGE equivalence in SAS so that I can put that code in the data step. Eventually I failed but if you guys know, please let me know. I'll try the above method today to see which one is the best.

Thanks again!

Thomas.K

New Contributor
Posts: 4

Re: Summary Table with Daily Total and Month to Date Total

Hi All

I just checked the method that Reeza provided and it worked! It looks like the other method from the other person may work too. But I'm too busy to check each and single of them. I'll keep those as references and might get back to them once I'm not that busy.

Thanks everyone!

Thomas.K

PROC Star
Posts: 1,146

Re: Summary Table with Daily Total and Month to Date Total

I believe that SUM OVER RANGE is a SQL extension that isn't supported in SAS. The SAS data step is a very different language from SQL; constructs that exist in the one don't tend to exist in the other.

Main thing is, you have the results you need!

Tom

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 2003 views
  • 0 likes
  • 6 in conversation