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.
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.
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;
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: