BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thomas_K
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
TomKari
Onyx | Level 15

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

thomas_K
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Patrick
Opal | Level 21

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;

Reeza
Super User

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

Tom
Super User Tom
Super User

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

thomas_K
Calcite | Level 5

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

thomas_K
Calcite | Level 5

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

TomKari
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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