Here's an example of how you do that. Look at the line where I calculate loadDate and add that in to my data before I append it to the main data set. As indicated in my comments as well, ideally I should also be checking if data for that day has already been loaded to ensure it doesn't load data twice, if there's an error and you run the code twice.
/*This program illustrates how to build a daily process that will add in your data and then create an automatic report every day
Author: F. Khurshed
Date: 2019-06-18
*/
data have;
input ID : 4. bucket1-bucket3 : 5.2;
format bucket: 5.2;
datalines;
1234 10.00 20.00 70.00
3456 10.00 20.00 70.00
1234 10.00 20.00 70.00
3456 10.00 20.00 70.00
1234 10.00 20.00 70.00
3456 10.00 20.00 70.00
;
run;
%macro addData(dsin=);
*should add a check to ensure data has not been loaded already;
*calculate summary statistics;
ods select none;
proc means data=&dsin stackods n sum;
class ID;
var bucket:;
ods output summary=_stats;
run;
ods select all;
**add in load date;
data _stats2;
set _stats;
loadDate=today();
format loadDate date9.;
rename N=Count SUM=TOtal;
keep ID Variable N SUM loadDate;
run;
*add data to master data set each day;
*this should go to a permanent library;
libname myData '/home/fkhurshed/Demo1';
proc append base=myData.reportingData data=_stats2 force;
run;
libname myData;
*removes temporary tables, uncomment once you are sure how it works;
*proc sql noprint;
*drop table _stats _stats2;
*quit;
%mend;
%macro createReport(path=);
*creates Excel file with data;
ods excel file="&path." style=meadow;
*report data for counts data;
title "Report for %sysfunc(today(), worddate.)";
title2 "Counts";
proc tabulate data=reportingData;
class loadDate Variable;
var Count;
table Variable='', loadDate=''*Count=''*n='#'*f=8.0;
run;
*report data for sum data;
title1;
title2 "Sums";
proc tabulate data=reportingData;
class loadDate Variable;
var TOtal;
table Variable='', loadDate=''*Total=''*sum='Total $'*f=8.0;
run;
ods excel close;
%mend;
%addData(dsin=have);
%createReport(path=/home/fkhurshed/Demo.xlsx);
... View more
