topic Re: Aggregating daily reports to create a month end report in SAS Programming

Aggregating daily reports to create a month end report

SASd15 — Tue, 07 Feb 2017 14:25:15 GMT

I have a daily report and below is the report

Date #oftransactions ExceptionsA ExceptionsB

01/01 50000 100 2

I would like to run it for everyday of the month and at the end of the month create the report below. This report uses both procSQL and Data steps. Also, daily data file is pulled from a database.

Date #oftransactions ExceptionsA ExceptionsB

01/01 50000 100 2

01/02 20000 20 1

01/03 10000 40 1

01/04 1000 5 0

01/05 6000 7 0

Thank you for taking the time to help.

Re: Aggregating daily reports to create a month end report

RW9 — Tue, 07 Feb 2017 14:37:33 GMT

You will want some sort of scheduler software to batch run your reports. Some SAS packages have this (maybe EG and DI?), otherwise you could use Window scheduler. Just set it to batch run your SAS program at the given dates, I would have two, one for daily run, one for monthly run.

Re: Aggregating daily reports to create a month end report

ballardw — Tue, 07 Feb 2017 15:10:21 GMT

Obviouslly we have no idea what your data looks like but sometimes when I see a comment like "procSQL and Data steps" I wonder if the process may be over complicating. See an example below that I think may replicate some of what your data may look like and a report step.

data have;
   informat date mmddyy10.;
   format date mmddyy10.;
   input date   account  transactions  exceptionsA   ExceptionsB;
datalines;
01/01/2017    1    170  8    2
01/01/2017    2    412  23   2
01/01/2017    3    527  19   3
01/02/2017    1    639  10   3
01/02/2017    2    448  24   5
01/02/2017    3    145  21   2
01/03/2017    1    140  24   0
01/03/2017    2    917  17   4
01/03/2017    3    498  18   2
01/04/2017    1    23   17   1
01/04/2017    2    918  19   3
01/04/2017    3    174  23   0
01/05/2017    1    37   6    2
01/05/2017    2    855  2    2
01/05/2017    3    565  23   4
01/06/2017    1    92   19   2
01/06/2017    2    598  12   1
01/06/2017    3    680  22   2
01/07/2017    1    396  19   2
01/07/2017    2    649  16   0
01/07/2017    3    97   5    1
01/08/2017    1    57   19   1
01/08/2017    2    39   9    2
01/08/2017    3    26   14   5
01/09/2017    1    840  9    2
01/09/2017    2    425  0    0
01/09/2017    3    266  14   2
01/10/2017    1    160  3    3
01/10/2017    2    848  20   0
01/10/2017    3    173  7    2
01/11/2017    1    155  9    5
01/11/2017    2    640  3    1
01/11/2017    3    872  16   3
01/12/2017    1    214  1    5
01/12/2017    2    242  25   0
01/12/2017    3    923  23   2
01/13/2017    1    554  17   2
01/13/2017    2    153  4    1
01/13/2017    3    657  12   0
01/14/2017    1    908  22   2
01/14/2017    2    136  14   2
01/14/2017    3    676  9    1
01/15/2017    1    799  23   3
01/15/2017    2    226  5    0
01/15/2017    3    320  9    1
01/16/2017    1    909  11   4
01/16/2017    2    562  1    0
01/16/2017    3    605  5    5
01/17/2017    1    66   14   2
01/17/2017    2    778  2    3
01/17/2017    3    437  14   0
01/18/2017    1    716  4    1
01/18/2017    2    47   24   3
01/18/2017    3    17   19   1
01/19/2017    1    989  16   3
01/19/2017    2    34   23   3
01/19/2017    3    346  10   4
01/20/2017    1    494  6    3
01/20/2017    2    122  6    0
01/20/2017    3    752  15   2
01/21/2017    1    840  18   3
01/21/2017    2    874  2    3
01/21/2017    3    594  21   2
01/22/2017    1    328  25   2
01/22/2017    2    317  12   4
01/22/2017    3    400  25   4
01/23/2017    1    666  10   0
01/23/2017    2    874  4    3
01/23/2017    3    82   6    0
01/24/2017    1    811  12   1
01/24/2017    2    415  1    0
01/24/2017    3    269  10   2
01/25/2017    1    765  7    0
01/25/2017    2    257  2    2
01/25/2017    3    246  23   4
01/26/2017    1    598  23   3
01/26/2017    2    95   11   1
01/26/2017    3    549  14   5
01/27/2017    1    905  21   3
01/27/2017    2    201  18   2
01/27/2017    3    839  4    1
01/28/2017    1    465  20   3
01/28/2017    2    569  15   1
01/28/2017    3    561  2    2
01/29/2017    1    531  7    5
01/29/2017    2    964  12   5
01/29/2017    3    548  23   4
01/30/2017    1    286  12   1
01/30/2017    2    707  0    2
01/30/2017    3    17   14   2
01/31/2017    1    140  18   3
01/31/2017    2    373  22   3
01/31/2017    3    838  6    3
;
run;

proc tabulate data=have;
   where month(date)=1;
   class date;
   format date mmddyy5.;
   var transactions ExceptionsA Exceptionsb;
   table date All='Month to Date Total',
         (transactions ExceptionsA Exceptionsb) * sum=''*f=best10.;
run;

Re: Aggregating daily reports to create a month end report

Shmuel — Tue, 07 Feb 2017 15:20:07 GMT

To create your daily report, it seems that you summarize daily data and then create the report, (one line per day)

something like:

     proc summarry data=....;
            output out=daily_data ...
    run;
    proc print  or proc report

You can aggregate you daily_date using proc append:

On first day of a month run:

proc datasets lib=<your library> nolist;
         delete monthly_data;
quit; run;

then everyday run just before creating the daily report: (aggregating the data)

proc append base = monthly_data
            data = daily_data;
run;

At the end of the month run your reporting code using <library>.monthly_data

instead the daily_data dataset.

Re: Aggregating daily reports to create a month end report

SASd15 — Sun, 12 Feb 2017 20:58:43 GMT

Please see additional details and clarification below:

I would like to run the report that yields the following record (1 row per day) for the past 6 months. Currently, i manually change the date on top of the report to run a report for a particular date in the past.

%LET REPDT = '01-Jan-2017'd;

Proc SQL (to extract data)

Data Step (to calculate variables)

Result:

Date Transactions Exceptions

1/1/17 700 60

However, i would like to run it for the past 6 months and yield results below:

Result:

Date Transactions Exceptions

1/1/17 700 60

1/2/17 10 0

1/3/17 2 1

Thanks again for taking the time.

Re: Aggregating daily reports to create a month end report

Shmuel — Sun, 12 Feb 2017 21:38:17 GMT

You can use next code to calculate start date:

%LET REPDT = 01Jan017;
data _null_;
    end_dt = input(&repdt,date9.);
    start_dt = intnx('month',end_dt,-6);
    call symput('enddt', strip(end_dt));
    call symput('startdt', strip(start_dt));
run;

proc sql;   /* extract data */ 
    create ...
    as select ...
    from dataset_name
    where date between &startdt and &enddt;
quit;

continu with calculations and reporting.